Access教学课件：第九章 VBA数据库编程技术

1、第9章 VBA数据库编程技术,9.1 数据访问接口ADO 9.2 ADO主要对象 9.3 VBA在Access中的数据库编程实例,第9章,本章小结 思考与练习,本章首先概述数据访问接口，接着将详细介绍ADO三个主要对象，最后通过实例说明VBA使用ADO对象访问Access数据库的具体方法与编程技巧。 本章的知识结构导航图如图9-1所示。,9.1 数据访问接口ADO,9.1.1 数据库引擎和接口,所谓数据库引擎实际上是一组动态链接库（DLL），当VBA程序运行时被连接到应用程序从而实现对数据库的数据的访问功能。数据库引擎（Database Engine）是应用程序与物理数据库之间的桥梁，是一种通

2、用接口的方式，用户可以使用统一形式和相同的数据访问与处理方法来访问各种类型数据库。 在VBA中主要提供了3种数据库访问接口：,9.1.1 数据库引擎和接口,1.ODBC API（Open DataBase Connectivity API，开放数据库互连应用程序接口） ODBC为关系数据库编程提供统一的接口，用户可通过它对不同类型的关系数据库进行操作。但是由于ODBC API允许对数据库进行比较接近底层的配置和控制，属底层数据库接口，在Access应用中，要直接使用ODBC API访问数据库则需要大量VBA函数原型声明和一些繁琐的、底层的编程。,9.1.1 数据库引擎和接口,2.DAO（Dat

3、a Access Objects，数据访问对象） DAO既提供了一组基于功能的API函数，也提供了一个访问数据库的对象模型，在Access数据库应用程序中，开发者可利用其中定义的如Database、QueryDef、RecordSet等一系列数据访问对象，实现对数据库的各种操作。,9.1.1 数据库引擎和接口,3.ADO（ActiveX Data Objects，动态数据对象） ADO是基于组件的数据库编程接口，它是一个与编程语言无关的COM组件系统，可以对来自多种数据提供者的数据进行操作。 ADO是对微软所支持的数据库进行操作的最有效和最简单直接的方法。,9.1.2 ADO,ADO以OLE

4、DB为基础，对OLE DB底层操作的复杂接口进行封装，使应用程序通过ADO极其简单的COM接口，就可以访问来自OLE DB数据源的数据，其中不论是关系数据库还是非关系数据库，也不论是本地数据库还是远程数据库，应用程序、ADO和数据源之间关系如图9-2所示。,9.1.2 ADO,图9-2 应用程序通过ADO访问不同数据源,9.1.2 ADO,与其他数据访问接口相比，ADO具有下列一些优点： 1.ADO能够访问各种支持OLE DB的数据源，包括数据库和文本文件、电子表格、电子邮件等数据源。 2.ADO采用了ActiveX技术，与具体的编程语言无关，任何使用如VC+、Java、VB、Delphi等高

5、级语言编写的应用程序都可使用ADO来访问各类数据源。 3.ADO将访问数据源的复杂过程抽象成几个易于理解的具体操作，并由实际对象来完成，因而使用起来简单方便。,9.1.2 ADO,4.ADO对象模型简单易用，速度快，资源开销和网络流量少，在应用程序和数据源之间使用最少的层数，为应用程序和数据源之间提供了轻便、快捷、高性能的接口。 5.ADO属应用层（高层）的编程接口，也可以在各种脚本语言（Script）中直接使用，特别适合于各种客户机/服务器应用系统和基于Web的应用，尤其在脚本语言中访问Web数据库是ADO的一大优势。,9.2 ADO主要对象,9.2.1 ADO对象模型,ADO对象模型是对A

6、DO对象集合的完整概括，它能够更加灵活、有效地发挥ADO对象的功能特性。ADO对象模型如图9-3所示，主要包含Connection、Recordset、Command、Parameter、Field、Property和Error共7个对象，ADO对象模型中有关对象、集合的说明见表9-1。 Connection、Recordset和Command是ADO对象模型中的三个最核心对象，也是应用程序访问数据源时使用最多的三个对象。,9.2.1 ADO对象模型,图9-3 ADO对象模型,9.2.1 ADO对象模型,9.2.2 Connection对象,Connection（连接）对象用于建立应用程序与指

7、定数据源的连接。 使用Connection对象实现与指定数据源连接的基本步骤为： （1）创建Connection对象； （2）设置Connection对象的连接字符串ConnectionString属性，用以指示要连接的数据源信息；,9.2.2 Connection对象,（3）打开Connection对象，实现应用程序与数据源的物理连接； （4）为节省系统资源，待对数据源中数据操作结束后，应关闭Connection对象，实现应用程序与数据源的物理断开。,9.2.2 Connection对象,建立应用程序与数据源的连接 首先要在应用程序中声明一个Connection对象，并对其进行初始化，具体方

8、法如下： Dim conn As ADODB.ConnectionSet conn= New ADODB.Connection 创建完Connection对象，在连接数据源之前，可利用Connection对象的ConnectionString属性设置建立连接的数据源信息。 设置ConnectionString属性的语法如下：,9.2.2 Connection对象,连接对象变量.ConnectionString=参数1=参数1值;参数2=参数2值; conn.ConnectionString=Provider=Microsoft. Jet.OLEDB.4.0;Data Source=D:DATA

9、学生成绩管理.mdb,ConnectionString包含Provider和Data Source两个参数，provider指定连接Access所使用的OLE DB程序为Microsoft.Jet. OLEDB.4.0； Data Source 指定连接的数据库文件路径。 ConnectionString属性为字符型。,9.2.2 Connection对象,接着就可以用Connection对象的Open方法实现应用程序与数据源的物理连接。Open方法的语法如下： 连接对象变量.Open ConnectionString,UserID,Password 或者 conn.Open Provider

10、=Microsoft.Jet. OLEDB.4.0;Data Source =D:DATA学生成绩管理.mdb 或者 conn.Open CurrentProject.Connection,9.2.2 Connection对象,待数据源中数据操作结束后，关闭Connection对象，实现应用程序与数据源的物理断开，以节省系统资源的开销。关闭Connection对象应使用Close方法，语法如下： 连接对象变量.Close,9.2.2 Connection对象,需要说明的是，使用Close方法只是关闭应用程序与数据源的物理连接，而Connection对象并未从内存中释放，要从内存中释放已有Con

11、nection对象应用如下语法： Set 连接对象变量=Nothing,总结：方法一 Dim conn As ADODB.Connection Set conn= New ADODB.Connection conn.ConnectionString=Provider=Microsoft.Jet. OLEDB.4.0;Data Source=D:DATA学生成绩管理.mdb conn.Open conn.Close Set conn=Nothing,9.2.2 Connection对象,总结：方法二 Dim conn As ADODB.Connection Set conn= New ADODB

12、.Connection conn.Open Provider=Microsoft.Jet.OLEDB.4.0; Data Source =D:DATA学生成绩管理.mdb conn.Close Set conn=Nothing,9.2.2 Connection对象,9.2.3 Recordset对象,Recordset（记录集）对象用于存储来自数据库中基本表或命令执行结果的记录全集。 Recordset对象中的数据在逻辑上由每行的记录和每列的字段组成，每个字段又表示为一个Field对象。任一时候，Recordset对象所指的当前记录均为记录全集中的单个记录。 类似于Connection对象，在

13、使用Recordset对象之前，也应声明并初始化一个Recordset对象，方法如下： Dim rs As ADODB.RecordsetSet rs= New ADODB.Recordset,9.2.3 Recordset对象,创建一Recordset对象之后，就可以通过Recordset对象的Open方法获取数据源操作的记录集。Open方法语法如下： 记录集对象变量.Open Source, ActiveConnection, CursorType, LockType, Options,9.2.3 Recordset对象,在数据库应用程序开发过程中，开发者可充分利用Recordset对象的

14、属性或方法实现应用程序对记录集中的数据操作，Recordset对象中常用属性及方法有: 1.ActiveConnection属性：通过设置ActiveConnection属性使打开的数据源连接与Connection对象相关联，该属性值为有效的Connection对象变量或包含ConnectionString参数的连接字符串。,9.2.3 Recordset对象,2.AbsolutePosition属性：返回Recordset对象当前记录的序号位置。 3.RecordCount属性：返回Recordset对象中记录的数目。 4.BOF、EOF属性：若当前位置在Recordset对象第一条记录之前

15、，BOF属性值为真，否则为假；若当前位置在Recordset对象最后一条记录之后，EOF属性值为真，否则为假。,9.2.3 Recordset对象,5.AddNew方法：该方法将在Recordset对象中添加一条新记录。语法如下： 记录集对象变量.AddNew 6.Delete方法：该方法将删除Recordset对象中当前记录，语法如下： 记录集对象变量.Delete,9.2.3 Recordset对象,7.Update方法：该方法将使Recordset对象中当前记录的更新内容保存到数据库中，语法如下： 记录集对象变量.Update 8.Move、MoveFirst、MoveLast、Move

16、Next、MovePrevious方法：当打开一个非空Recordset对象时，当前记录总是定位在第一条记录，可根据需要使用这些方法灵活地将记录指针移动到指定位置，记录定位语法见表9-6。,9.2.3 Recordset对象,9.2.3 Recordset对象,9.Close方法：该方法用以关闭一个已打开的Recordset对象，并且释放相关的数据和为该Recordset对象所申请的资源。语法如下： 记录集对象变量.Close 由于使用Close方法只释放相关的系统资源，Recordset对象并未从内存中释放，若还需要打开该Recordset对象，可直接使用该对象的Open方法。若要将Reco

17、rdset对象从内存中完全释放，应设置Recordset对象为Nothing，语法如下： Set 记录集对象变量=Nothing,9.2.4 Command对象,Command（命令）对象用以定义并执行针对数据源的具体命令，即通过传递指定的SQL命令来操作数据库，如建立数据表、删除数据表或修改数据表结构等；应用程序也可通过Command对象查询数据库，并将Command对象的运行结果返回给Recordset对象，以便进一步执行如增加、删除、更新、筛选记录等操作。,9.2.4 Command对象,创建一个Command对象之后，就可利用该对象的属性及方法对数据源提出命令请求，Command对象的

18、常用属性和方法有： 1.ActiveConnection属性：通过设置ActiveConnection属性使已打开的数据源连接与Connection对象相关联，ActiveConnection属性值可以是有效的Connection对象变量或包含ConnectionString参数的连接字符串。,9.2.4 Command对象,2.CommandText属性：用以表示Command对象要对数据源下达的命令，通常设置为能够完成某个特定功能的SQL语句、数据表或存储过程的调用等。 3.Execute方法：这是Command对象的最主要方法，用以执行一个由CommandText属性指定的查询、SQL语

19、句或存储过程。,9.3 VBA在Access中的 数据库编程实例,9.3.1 在VBA中引用ADO类库,ADO是采用面向对象方法设计的，ADO各个对象的定义都被集中在ADO类库中。要使用ADO对象，首先要引用ADO类库。在VBA中引用ADO类库的操作步骤如下： 1.在VBE环境中，选择“工具”菜单下的“引用”命令，打开“引用”对话框，如图9-4所示。 2.在“可使用的引用”列表框中选中“Microsoft ActiveX Data Objects 2.1”复选框。另外，在这个对话框中还可以改变被引用类库的优先级。例如，选中“Microsoft ActiveX Data Objects 2.1”

20、这一类库，单击“优先级”的向上按钮，可提升ADO类库的优先级。,9.3.1 在VBA中引用ADO类库,图9-4 “引用”对话框,9.3.2 VBA数据库编程实例,【例9-1】打开“学生成绩管理.mdb”，设计“增加新课程”窗体，运行界面如图所示，要求实现： 1.在窗体中分别输入课程编号、课程名称、学时、学分，显示在对应文本框中。 2.单击“添加”按钮，则判断输入的“课程编号”在“课程”表中是否已经存在，若未存在，则将此记录添加至“课程”表中，否则显示提示信息。,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,【例9-2】打开“学生成绩管理.

21、mdb”，设计“按专业编号查询编辑专业信息”窗体，运行界面如图所示，要求实现： 1.在窗体组合框Combo1中选择一专业编号，则“专业名称”、“专业负责人”显示在对应文本框中。 2.单击“更新”按钮，则弹出提示信息询问是否要更新数据，若确定要，则对“专业”表中的信息进行更新。,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,【例9-3】打开“学生成绩管理.mdb”，设计“课程成绩统计”窗体，运行界面如图所示，要求实现： 1.在组合框Combo1中选择一课程编号，则对应的课程名称、学分、任课教师、学时分别显示

22、在对应文本框中。 2.单击“统计”按钮，则在对应文本框中显示指定课程的所有参考学生人数、课程平均分、60分以上人数和不及格人数。 3.若未指定具体课程编号就单击“统计”按钮，则显示提示信息。,9.3.2 VBA数据库编程实例,图9-5 课程成绩统计窗体,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,9.3.2 VBA数据库编程实例,设计步骤如下： （1）创建窗体并添加相关控件。控件的主要属性如表9-7所示。 （2）利用组合框向导设置Combo1对象的值来源。设置步骤详见图9-6。 （3）对组合框Combo1的Change事件编写如下代码： Private Sub Comb

23、o1_Change() Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset 从 课程和教师数据表中获取指定课程编号的记录,9.3.2 VBA数据库编程实例,Dim SQLstr As StringSQLstr = Select 课程.课程名称,课程.学时,课程.学分,教师.姓名 From 课程,教师 Where 课程.教师编号=教师.教师编号 And 课程.课程编号= & Me.Combo1 & rs.Open SQLstr, CurrentProject.Connection, 2, 2 判断是否获取指定的记录 If Not rs.E

24、OF() Then Me.Text1 = rs(课程名称) Me.Text2 = rs(学分) Me.Text3 = rs(姓名) Me.Text4 = rs(学时)End Ifrs.CloseSet rs = Nothing End Sub,9.3.2 VBA数据库编程实例,（4）对“统计”按钮Command1的Click事件编写如下代码： Private Sub Command1_Click()Dim rs As ADODB.RecordsetSet rs = New ADODB.Recordset 从 成绩 数据表获取指定课程编号的所有记录 Dim SQLstr As StringSQL

25、str = Select * From 成绩 Where 课程编号= & Me.Combo1 & rs.Open SQLstr, CurrentProject.Connection, 2, 2 判断获取的记录集是否为空,非空则统计各项数据,9.3.2 VBA数据库编程实例,If Not rs.BOF() Or Not rs.EOF() Then sum = 0 n = 0 x = 0 y = 0 逐条记录累加成绩,并分别统计及格与不及格人数 Do While Not rs.EOF() sum = sum + rs(成绩) n = n + 1 If rs(成绩) = 60 Then x = x + 1 Else y = y + 1 End if,9.3.2 VBA数据库编程实例,rs.MoveNextLoopaver = sum / nMe.Text5 = nMe.Text6 = Int(aver * 100 + 05) / 100Me.Text7 = xMe.Text8 = y Else未指定课程编号则提示信息MsgBox 课程编号为空,请重新指定,0 +16,提示 End Ifrs.CloseSet rs = Nothing E