第10章数据库VBA的数据库编程

1、 在开发在开发AccessAccess数据库应数据库应用系统时，为了能开发出用系统时，为了能开发出更实用、更有效的更实用、更有效的AccessAccess数数据库应用程序，以便能快据库应用程序，以便能快速、有效地管理好数据，速、有效地管理好数据，还应当学习和掌握还应当学习和掌握VBAVBA的的数据库编程方法。数据库编程方法。退出退出10.1 10.1 数据库访问接口数据库访问接口10.2 10.2 数据访问对象（数据访问对象（DAODAO）10.3 ActiveX10.3 ActiveX数据对象（数据对象（ADOADO） 为了在为了在VBA程序代码中能方便地实现对数据库的数程序代码中能方便地实

2、现对数据库的数据访问功能，据访问功能，VBA语言提供有相应的通用的接口方式。语言提供有相应的通用的接口方式。 VBA是通过是通过 Microsoft Jet 数据库引擎工具来支持数据库引擎工具来支持对数据库的访问。所谓数据库引擎实际上是一组动态链对数据库的访问。所谓数据库引擎实际上是一组动态链接库（接库（DLL），当程序运行时被连接到），当程序运行时被连接到 VBA 程序而实程序而实现对数据库的数据访问功能。数据库引擎是应用程序与现对数据库的数据访问功能。数据库引擎是应用程序与物理数据库之间的桥梁，它以一种通用接口的方式，使物理数据库之间的桥梁，它以一种通用接口的方式，使各种类型物理数据库对用

3、户而言都具有统一的形式和相各种类型物理数据库对用户而言都具有统一的形式和相同的数据访问与处理方法。同的数据访问与处理方法。 在在VBA语言中，提供了如下三种基本的数据库访问接口语言中，提供了如下三种基本的数据库访问接口: （1）开放数据库互连应用编程接口（）开放数据库互连应用编程接口（简称简称ODBC API）。）。 （2）数据访问对象（）数据访问对象（简称简称DAO）。）。 （3）Active数据对象（数据对象（简称简称ADO）。）。 在在VBA语言程序设计中，通过数据库引擎可以访问如下语言程序设计中，通过数据库引擎可以访问如下三种类型的数据库：三种类型的数据库： （1）本地数据库，即是）本

4、地数据库，即是 Access 数据库。数据库。 （2）外部数据库，即是所有的索引顺序访问方法）外部数据库，即是所有的索引顺序访问方法 (ISAM)数据库。数据库。 （3）ODBC数据库，即是符合开放数据库连接数据库，即是符合开放数据库连接 (ODBC)标准的数据库，例如，标准的数据库，例如，Oracle、 Microsoft SQL Server等。等。 数据访问对象（数据访问对象（DAO）是）是VBA语言提供的一种数据语言提供的一种数据访问接口。包括数据库、表和查询的创建等功能，通过运访问接口。包括数据库、表和查询的创建等功能，通过运行行VBA程序代码可以灵活地控制数据访问的各种操作。程序代

5、码可以灵活地控制数据访问的各种操作。 当用户在当用户在Access模块设计中要使用模块设计中要使用DAO的访问对象的访问对象时，首先应该增加一个对时，首先应该增加一个对DAO库的引用。库的引用。Access 2003的的DAO引用库为引用库为DAO3.6，其引用设置方法为：先进入，其引用设置方法为：先进入VBA编程环境，即打开编程环境，即打开VBE窗口，单击菜单栏中的窗口，单击菜单栏中的“工工具具”，单击，单击“工具工具”菜单中的菜单中的“引用引用”项，弹出项，弹出“引用引用”对话框，如图对话框，如图10-1所示，从所示，从“可使用的引用可使用的引用”的列表项中，的列表项中，选中选中“Micr

6、osoft DAO 3.6 Object Library”项，然后，项，然后，单击单击“确定确定”按钮。按钮。 DAO 模型的分层结构图如图模型的分层结构图如图10-2所示。它包含了一个所示。它包含了一个复杂的可编程数据关联对象的层次，其中复杂的可编程数据关联对象的层次，其中 DBEngine对象对象处于最顶层，它是模型中惟一不被其他对象所包含的数据处于最顶层，它是模型中惟一不被其他对象所包含的数据库引擎本身。层次低一层对象是，库引擎本身。层次低一层对象是，Errors和和 Workspaces对象。层次再低一层对象如对象。层次再低一层对象如Errors对象的低一层对象是对象的低一层对象是Er

7、ror；Workspaces 对象的低一层对象是对象的低一层对象是 Workspace。Databases 的低一层对象是的低一层对象是 Database。Database 的低的低一层对象是一层对象是 Containers、 QueryDefs、RecordSets、Relations 和和 TableDefs。TableDefs对象的低一层对象是对象的低一层对象是 TableDef。同理如此类推，在此不作详列。同理如此类推，在此不作详列。 其中对象名的尾字符为其中对象名的尾字符为“s”的那些对象（如，的那些对象（如，Errors、Workspaces、Databases、TableDefs

8、、Fields 等）是等）是集合对象，集合对象下一层包含其成员对象。集合对象，集合对象下一层包含其成员对象。 DAO 对象必须通过对象必须通过 VBA 程序代码来控制和操作。在代码程序代码来控制和操作。在代码中，必须设置对象变量，然后再通过对象变量使用其下的对象、中，必须设置对象变量，然后再通过对象变量使用其下的对象、或者对象的属性和方法。或者对象的属性和方法。 1对象变量的声明对象变量的声明 同普通变量的声明一样，声明的关键字可以是同普通变量的声明一样，声明的关键字可以是 Dim、Private、Public 等。等。 声明对象变量的语句格式：声明对象变量的语句格式： Dim 对象变量名对象

9、变量名 As 对象类型对象类型 例如：例如： Dim wks As Workspace 声明声明wks为工作区对象变量为工作区对象变量 Dim dbs As Database 声明声明dbs为数据库对象变量为数据库对象变量 Dim 只是声明了对象变量的类型，对象变量的值只是声明了对象变量的类型，对象变量的值必须通过必须通过 Set 赋值语句来赋值。赋值语句来赋值。Set赋值语句的格式：赋值语句的格式： Set 对象变量名称对象变量名称 = 对象指定声明对象指定声明例如：例如： Set wks = DBEngine.Workspaces (0) 打开默认工作区（即打开默认工作区（即0号工作区）号

10、工作区）Set dbs = wks.OpenDatabase(e:Access职工管理职工管理.mdb) 打开数据库打开数据库 例例10-1 通过通过 DAO 编程，显示当前打开的数据库的编程，显示当前打开的数据库的名称。名称。Private Sub Cmd1_Click() Dim wks As Workspace 声明工作区对象变量声明工作区对象变量 Dim dbs As Database 声明数据库对象变量声明数据库对象变量 Set wks = DBEngine.Workspaces(0) 打开默认工作区（即打开默认工作区（即0号工作区）号工作区） Set dbs = wks.Datab

11、ases(0) 打开当前数据库（即打开当前数据库（即0号数据库）号数据库） MsgBox dbs.Name Name 是是 Database 对象变量的属性对象变量的属性End Sub 1Database 对象的常用属性和方法对象的常用属性和方法 Database 对象代表数据库。对象代表数据库。Database对象的常对象的常用属性如表用属性如表10-2所示。所示。Database对象的常用方法如表对象的常用方法如表10-3所示。所示。表表10-2 Database对象的常用属性对象的常用属性 表表10-3 Database对象的常用方法对象的常用方法 使用使用 TableDef 对象的对象

12、的 CreateField 方法可创建表方法可创建表中的字段。中的字段。 使用使用 CreateField方方 法的语句格式：法的语句格式： Set fed = tbe.CreateField(name, type, size) 其中：其中： fed 是字段对象变量。是字段对象变量。tbe 是表对象变量。是表对象变量。name是字段名。是字段名。type 是字段的数据类型，要用英文字符表是字段的数据类型，要用英文字符表示，如示，如 dbText 表示文本型，表示文本型，dbInteger 表示整型。表示整型。size 表示字段大小。表示字段大小。 RecordSet 对象代表一个表或查询中的所

13、有对象代表一个表或查询中的所有记录。记录。RecordSet 对象提供了对记录的添加、对象提供了对记录的添加、删除和修改等操作的支持。删除和修改等操作的支持。 RecordSet 对象的常用属性如表对象的常用属性如表10-4所示。所示。 RecordSet 对象的常用方法如表对象的常用方法如表10-5所示。所示。表表10-4 RecordSet对象的常用属性对象的常用属性 表表10-5 RecordSet 对象的常用方法的方法对象的常用方法的方法 在在 VBA 编程中，利用编程中，利用 DAO 实现对数据库实现对数据库访问时，要先创建对象变量，再通过对象方法访问时，要先创建对象变量，再通过对象

14、方法和属性来进行操作。和属性来进行操作。 下面通过例子介绍利用下面通过例子介绍利用 DAO 实现对数据库实现对数据库访问的一般语句和步骤：访问的一般语句和步骤： 例例10-2 编写一个使用编写一个使用DAO的名为的名为 UseDaoUpdateAge 的子的子过程，通过调用该子过程来完成对过程，通过调用该子过程来完成对“职工管理职工管理”数据库的数据库的“职工职工基本资料基本资料”表的年龄字段值都加表的年龄字段值都加1的操作。（假设的操作。（假设“职工管职工管理理.mdb”数据库文件存放在数据库文件存放在E盘盘“E:Access”文件夹中，文件夹中，“职工职工基本资料基本资料”表中的表中的“年

15、龄年龄”字段的数据类型是整型）。本例的窗字段的数据类型是整型）。本例的窗体名称为体名称为“例例10-2 使用使用DAO编程编程-年龄加年龄加1”。 Cmd1 命令按钮的单击事件过程和命令按钮的单击事件过程和 UseDaoUpdateAge 子过子过程的程的VBA程序代码如下：程序代码如下： 例例10-3 通过在通过在VBA程序中使用程序中使用DAO，在，在当前数据库中创建一个名为当前数据库中创建一个名为“用户表用户表”的表。的表。“用户表用户表”的表结构如表的表结构如表10-6所示。该表的主所示。该表的主键是键是“用户用户ID”字段。本例的窗体名是字段。本例的窗体名是“例例10-3 用用 DA

16、O 创建数据表创建数据表”，窗体中的命令按钮，窗体中的命令按钮名称是名称是Cmd1。 例例10-4 通过在通过在VBA程序中使用程序中使用DAO，实现，实现往当前数据库中的往当前数据库中的“用户表用户表”添加新记录。本添加新记录。本例的窗体名是例的窗体名是“例例10-4 用用DAO往用户表添加记往用户表添加记录录”，如图，如图10-3所示。所示。 ActiveX数据对象（数据对象（ActiveX Data Objects ，简，简称称ADO）是基于组件的数据库编程接口，它可以对来）是基于组件的数据库编程接口，它可以对来自多种数据提供者的数据进行读取和写入操作。自多种数据提供者的数据进行读取和写

17、入操作。 在在 Access 模块设计时要使用模块设计时要使用 ADO 的各个访问的各个访问对象，也应该增加对对象，也应该增加对 ADO 库的引用。库的引用。Access 2003 的的 ADO 引用库为引用库为 ADO2.1，其引用设置方式为：先进，其引用设置方式为：先进入入 VBA 编程环境编程环境VBE，单击菜单栏中的，单击菜单栏中的“工具工具”，单击选择单击选择“工具工具”菜单中的菜单中的“引用引用”项，弹出项，弹出“引用引用”对话框，对话框， 从从“可使用的引用可使用的引用”列表框选择项中选中列表框选择项中选中“Microsoft ActiveX Data Objects 2.1 L

18、ibrary” 并按并按“确定确定”按钮即可。按钮即可。 ADO对象模型图如图对象模型图如图10-4所示，它提供一系列所示，它提供一系列数据对象供使用。不过，数据对象供使用。不过，ADO 接口与接口与 DAO不同，不同，ADO对象不须派生，大多数对象都可以直接创建对象不须派生，大多数对象都可以直接创建（Field 和和 Error 除外），没有对象的分级结构。除外），没有对象的分级结构。 使用时，只需在程序中创建对象变量，并通过对使用时，只需在程序中创建对象变量，并通过对象变量来调用访问对象方法、设置访问对象属性，这象变量来调用访问对象方法、设置访问对象属性，这样就实现对数据库的各项访问操作。

19、样就实现对数据库的各项访问操作。ADO 只需要九只需要九个对象和四个集合（对象）就能提供其整个功能。个对象和四个集合（对象）就能提供其整个功能。表表10-7 ADO对象说明对象说明 ADO 对象必须通过对象必须通过 VBA 程序代码来控制和操作。在代码程序代码来控制和操作。在代码中，必须设置对象变量，然后再通过对象变量使用其下的对象、中，必须设置对象变量，然后再通过对象变量使用其下的对象、或者对象的属性和方法。或者对象的属性和方法。 声明对象变量的语句格式：声明对象变量的语句格式： Dim 对象变量名称对象变量名称 As ADODB.对象类型对象类型 例如：例如：Dim con As New

20、ADODB.Connection 声明一个连接对象变量声明一个连接对象变量Dim res As New ADODB.RecordSet 声明一个记录集对象变量声明一个记录集对象变量 注意，注意，ADODB 是是 ADO 类型库的短名称，用于识别与类型库的短名称，用于识别与DAO中同名的对象。例如，中同名的对象。例如，DAO中有中有 RecordSet 对象，对象，ADO 中也中也有有 RecordSet对象，为了能够区分开来，在对象，为了能够区分开来，在 ADO 中声明中声明RecordSet 类型对象变量时，用上类型对象变量时，用上 ADODB.RecordSet。总之，。总之，在在ADO中

21、声明对象变量时，一般都要用上中声明对象变量时，一般都要用上“ADODB.”前缀。前缀。1Connection 对象的常用方法对象的常用方法（1）Open方法方法通过使用通过使用Connection 对象的对象的Open方法来建立与数据源的连接。方法来建立与数据源的连接。 Open方法的语句格式：方法的语句格式：Dim 连接对象变量名连接对象变量名 As New ADODB.Connection连接对象变量名连接对象变量名.Open ConnectionString, UserID, Password, OpenOptions 例如，创建与罗斯文示例数据库例如，创建与罗斯文示例数据库“North

22、wind.mdb”连接的语句：连接的语句：Dim cnn As New ADODB.Connection 声明一个连接对象变量声明一个连接对象变量 cnncnn.Open Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:Program FilesMicrosoft OfficeOFFICE11SAMPLES Northwind.mdb（2）Close方法方法 通过使用通过使用 Connection 对象的对象的 Close 方法来关闭与数方法来关闭与数据源的连接。据源的连接。 Close方法的语句格式：方法的语句格式： 连接对象变量名连接

23、对象变量名.Close 注意，该语句可以关闭注意，该语句可以关闭Connection 对象，断开应用对象，断开应用程序与数据源的连接。但是程序与数据源的连接。但是Connection 仍在内存中，释仍在内存中，释放放 Connection 对象变量的方法是使用下面语句格式的语对象变量的方法是使用下面语句格式的语句：句： 释放连接对象变量的语句格式：释放连接对象变量的语句格式： Set连接对象变量名连接对象变量名= nothing 例如，关闭与例如，关闭与“罗斯文示例数据库罗斯文示例数据库.mdb”连接的语句：连接的语句：cnn. CloseSet cnn = nothing（3）如果指定的数据

24、源就是当前已经打开的数据库，则）如果指定的数据源就是当前已经打开的数据库，则必须通过必须通过 CurrentProject 对象的对象的Connection属性来取得属性来取得连接。连接。 语句格式：语句格式：Dim 连接对象变量名连接对象变量名 As New ADODB.ConnectionSet 连接对象变量名连接对象变量名= CurrentProject.Connection 例如：创建与当前已经打开的数据库的连接：例如：创建与当前已经打开的数据库的连接：Dim cnn As New ADODB.Connection Set cnn = CurrentProject.Connection

25、建立建立 Recordset 对象的语句格式：对象的语句格式： Dim 记录集对象变量名记录集对象变量名 As ADODB.Recordset Set 记录集对象变量名记录集对象变量名 = New ADODB.Recordset RecordSet 对象的常用属性如表对象的常用属性如表10-8所示。所示。 RecordSet对象的常用方法如表对象的常用方法如表10-9所示。所示。 （1）建立）建立 Command 对象对象 建立建立 Command 对象的语句格式：对象的语句格式： Dim 对象变量名对象变量名 As ADODB.Command Set 对象变量名对象变量名 = New ADODB.Command（2）Co