Access VBA数据库编程(ADO) 详细

1、第7章 VBA数据库编程,1,本章内容,记录集概述 在Access中引用ADO对象 引用记录字段 浏览记录 编辑数据 用ADO技术实现复杂查询 在VBA程序中使用SQL命令 访问当前数据库以外的数据库 综合实例编制“研究生成绩管理与统计”程序,2,7.1 记录集概述,7.1.1 ADO的9个对象 7.1.2 了解记录集,3,7.1.1 ADO的9个对象,ADO：ActiveX Data Objects Access内嵌的VBA是用ADO技术开发数据库应用的主要工具 ADO对象模型有9个对象： Connection、Recordset、Record、Command、Parameter、Field

2、、Property、Stream、Error 常用对象：Connection、Command、Recordset,4,7.1.1 ADO的9个对象,Connection对象：ADO对象模型中最高级的对象，实现应用程序与数据源的连接。 Command对象：主要作用是在VBA中通过SQL语句访问、查询数据库中的数据。 Recordset对象：存储访问表和查询对象返回的记录。使用该对象，可以浏览记录、修改记录、添加新的记录或者删除特定的记录。 Recordset对象的功能最常用、最重要,5,7.1.1 ADO的9个对象,ADO的3个对象之间互有联系： Command对象和Recordset对象依赖于

3、Connection对象的连接； Command对象结合SQL命令可以取代Recordset对象，但远没有Recordset对象灵活、实用； Recordset对象它只能实现数据表内记录集操作，无法完成表和数据库的数据定义操作； 数据定义操作一般需通过Command对象用SQL命令完成。本章用DoCmd对象代替Command对象。,6,7.1.2 了解记录集,记录集(Recordset)：对表执行查询操作时，返回的一组特定记录。 用记录集可执行的操作：对表中的数据进行查询和统计，在表中添加、更新或删除记录。 记录集是一个对象，它包括记录和字段，具有其特定的属性和方法，利用这些属性和方法就可以编

4、程处理数据库中的记录。,7,7.2 在Access中引用ADO对象,应用程序中的ADO引用：声明Connection对象创建Recordset对象编程完成各种数据访问操作 7.2.1 声明Connection对象 7.2.2 声明与打开Recordset对象 7.2.3 关闭Recordset和Connection对象,8,7.2.1 声明Connection对象,(1) 声明一个Connection对象 (2) 初始化Connection对象（决定Connection对 象与哪个数据库相连接） 例如： Dim cnGraduate As ADODB.Connection Set cnGrad

5、uate=CurrentProject.Connection,9,7.2.2 声明与打开Recordset对象,(1) 声明Recordset对象 (2) 创建Recordset对象实例 (3) 打开Recordset对象 例如： Dim rsStudents As ADODB.Recordset Set rsStudents=New ADODB.Recordset rsStudents.Open 研究生, cnGraduate, , , adCmdTable,10,7.2.3 关闭Recordset和Connection对象,方法：执行Recordset 对象和Connection对象的Cl

6、ose方法 将对象设置为Nothing 例如： rsStudents.Close cnGraduate.Close Set rsStudents=Nothing Set cnGraduate=Nothing 上述语句不是必须的。应用程序终止运行时，系统会自动关闭并清除这两个对象。,11,7.3 引用记录字段,任何对记录集的访问都是针对当前记录进行的。打开记录集时默认的当前记录为第1条记录。 引用记录的字段： 直接在记录集对象中引用字段名称，如 Code=rsStudents!学号 若记录集字段名包含空格，或者字段名是一个保留字， 则引用时必须将该字段名用方括号括起来。 使用记录集对象的Fiel

7、ds(n)属性。n是记录中字段从左到右的排列序号，第一个字段的序号为0。如 Code=rsStudents.Fields(0),记录集对象与字段名间的连接符,12,7.3 引用记录字段,例7-1 建立名为ADO的模块，编写如下过程。运行该过程后，输出对话框显示“导师”表中第一位教师的编号和姓名。,13,7.3 引用记录字段,记录集更多的应用是在窗体对象上：建立一个空白窗体设计各个控件编程引用记录集当前记录的相关字段或将字段的值通过控件显示 注意：如果涉及数据访问的事件过程不止一个，可在代码窗口的通用段定义Connection对象和Recordset对象，然后在Form_Load事件过程中完成数

8、据库连接和数据表的打开。,14,7.3 引用记录字段,例7-2,运行：分别单击“导师编号”和“导师姓名”按钮,窗体设计视图 (删除导航按钮),15,7.3 引用记录字段,例7-2窗体程序代码,16,7.4 浏览记录,Recordset记录集对象提供了4种记录指针的移动方法 MoveFirst 记录指针移到第一条记录 MoveNext 记录指针移到当前记录的下一条记录 MovePrevious 记录指针移到当前记录的上一条记录 MoveLast 记录指针移到最后一条记录 Recordset记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置 记录指针将指向最后一条记录之后，EOF属

9、性为True 记录指针将指向第一条记录之前，BOF属性为True BOF和EOF属性的值均为True，表示记录集为空,17,7.4 浏览记录,例7-3 对例7-2进行修改,增加如下事件过程代码： Private Sub Command3_Click() 单击“下一个记录”按钮 rsTeacher.MoveNext End Sub,18,7.4 浏览记录,上面的过程隐含错误：移至最后一条记录后无法再下移指针，将出现运行错误信息框。 两种纠正错误的方法： 如果记录集的EOF属性为True，就回到第一条记录 如果记录集的EOF属性为True，就回到最后一条记录,Private Sub Command

10、3_Click() 单击“下一个记录”按钮 rsTeacher.MoveNext End Sub,19,7.4 浏览记录,20,7.4 浏览记录,方法2的程序还将运行出错。因为Recordset对象有一个名为LockType的属性，默认值为adLockReadOnly，此时只能浏览记录数据，记录的移动方式也只能是MoveNext和MoveFirst。 要实现记录指针的4种移动操作，完成记录的添加、删改或更新，必须在使用Open方法打开数据表之前，将该记录集对象的LockType属性设置成adLockPessimistic或adLockOptimistic，如 rsTeacher.LockTyp

11、e = adLockPessimistic rsTeacher.Open 导师, cnGraduate, , , adCmdTable,21,7.4 浏览记录,Recordset对象的LocyType属性决定数据的锁定方式： adLockReadOnly：数据处于只读状态。 adLockPessimistic(保守式锁定)：编辑数据时即锁定数据源记录，直到数据编辑完成才释放。 adLockOptimistic(开放式锁定)：编辑数据时不锁定，用Update方法提交数据时才锁定数据源记录。 adLockBatchOptimistic(开放式更新)：应用于批更新模式,22,7.4 浏览记录,如果数

12、据表中没有记录，即BOF和EOF同时为True，就不能引用表中的数据。 将单击“导师姓名”按钮的事件过程改写如下 Private Sub Command2_Click() If rsTeacher.BOF = True And rsTeacher.EOF = True Then Text1.Value = Else Text1.Value = rsTeacher!姓名 End If End Sub 同理，修改单击“导师编号”按钮的事件过程 同理，修改“下一个记录”按钮的事件过程代码(空表情况下记录指针不移动),23,7.5 编辑数据,7.5.1 用ADO记录集的AddNew方法添加记录 7.5

13、.2 用ADO记录集的Update方法修改记录 7.5.3 用ADO记录集的Delete方法删除记录,24,7.5.1 用ADO记录集的AddNew方法添加记录,添加新记录的步骤： （1）用记录集的AddNew方法产生一个空记录 （2）为空记录的各个字段赋值 （3）用记录集的Update方法保存新记录 例7-4 在例7-3的基础上进行修改,25,7.5.1 用ADO记录集的AddNew方法添加记录,添加的程序代码:,Private Sub Command4_Click() Dim Age As Byte rsTeacher.MoveFirst Age = rsTeacher!年龄 读取第一条记

14、录的年龄字段值 rsTeacher.AddNew 添加一条新记录 rsTeacher!导师编号 = 107 rsTeacher!姓名 = 高原 rsTeacher!年龄 = Age rsTeacher.Update End Sub,思考：什么情况下单击“新记录”按钮，系统会提示出错 ？,26,7.5.2 用ADO记录集的Update方法修改记录,修改记录的步骤： （1）将记录指针移动到需要修改的记录上 （2）对记录中的相关字段的值进行修改 （3）用Update方法保存更改 修改后的结果不得违反数据完整性约束 例7-5 在模块ADO中添加过程Update_Age：将 “导师”表中第5条记录的年龄

15、字段值修改为60岁。,注意设置记录集的LockType类型,27,7.5.2 用ADO记录集的Update方法修改记录,Sub Update_Age() Dim I As Byte Dim cnGraduate As ADODB.Connection Set cnGraduate = CurrentProject.Connection Dim rsTeacher As ADODB.Recordset Set rsTeacher = New ADODB.Recordset rsTeacher.LockType = adLockPessimistic rsTeacher.Open 导师, cnGr

16、aduate, , , adCmdTable 向下跳过4条记录，将记录指针指向第5条记录 For I = 1 To 4 rsTeacher.MoveNext Next I rsTeacher!年龄 = 60 : rsTeacher.Update End Sub,28,7.5.2 用ADO记录集的Update方法修改记录,例7-6在例7-4的基础上进行修改 添加的程序代码:,Private Sub Command5_Click() rsTeacher.MoveFirst Do While Not rsTeacher.EOF If rsTeacher!性别 = 男 Then rsTeacher!年

17、龄 = rsTeacher!年龄 + 1 rsTeacher.Update End If rsTeacher.MoveNext Loop End Sub,29,7.5.3 用ADO记录集的Delete方法删除记录,删除记录要慎重，因为被删记录无法恢复 删除记录的步骤: （1）移动到需要删除的记录上 （2）用记录集对象的Delete方法删除当前记录 （3）将某条记录指定为当前记录 删除记录后，Access不能自动使下一条记录成为当前记录。根据不同情况，可用MoveNext或MoveLast方法定位记录指针。,30,7.5.3 用ADO记录集的Delete方法删除记录,例7-7 对例7-6添加如下

18、的单击窗体事件过程：,31,7.5.3 用ADO记录集的Delete方法删除记录,If rsTeacher!姓名 = 高原 Then Flag = MsgBox(是否要删除高原？, vbYesNo, 删除确认) If Flag = vbYes Then 确定删除 rsTeacher.Delete MsgBox 记录删除完毕。 rsTeacher.MoveNext 删除记录后设定新的当前记录 If rsTeacher.EOF Then rsTeacher.MoveLast Exit Sub ElseIf Flag = vbNo Then 不删除记录 MsgBox 放弃删除操作！, , 删除确认

19、Exit Sub End If End If,如果找到“高原”，进行相关操作，然后退出过程,32,7.6 用ADO技术实现复杂查询,例7-8 在ADO模块中建立Sex过程：统计并在输出对话框中显示男、女研究生人数比。要求以人数多的一方为1、放在右侧、保留小数两位。,程序分析：先遍历全部记录，分别统计出男、女研究生人数。如果男生人数多于女生，则男生人数为1，女生人数改为女生人数除以男生人数；反之亦然。,33,7.6 用ADO技术实现复杂查询,Sub Sex() （定义对象、完成与数据库的连接、打开研究生表、定义变量） Student.MoveFirst Do While Not Student.

20、EOF 统计男、女研究生人数 If Student!性别 = 男 Then Boy = Boy + 1 If Student!性别 = 女 Then Girl = Girl + 1 Student.MoveNext Loop If Girl = Boy Then 以男生人数为1 MsgBox 女:男= Data Source=;User ID=用户标识;Password=密码; 其中： 提供者为Microsoft.Jet.OLEDB.4.0 数据库名包括数据库所在的路径及.mdb文件的名字 用户标识是用户的名字，缺省为admin 密码省略则表示没有密码,53,7.8 访问当前数据库以外的数据库,例7-30 在SQL模块中编写过程Double_Database （Department表保存在C:Manage.mdb数据库中）,Sub Double_Database() (定义对象、连接数据库、设置记录集LockType属性、打开导师表) 建立与另一个数据库Department的连接 Dim Dept As ADODB.Connection Set Dept = New ADODB.Connection Dept.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Manage.mdb;User ID=;P