1、第7章 VBA数据库编程,本章内容,记录集概述在Access中引用ADO对象 引用记录字段 浏览记录 编辑数据 用ADO技术实现复杂查询在VBA程序中使用SQL命令访问当前数据库以外的数据库综合实例编制“研究生成绩管理与统计”程序,7.1 记录集概述,7.1.1 ADO的9个对象 7.1.2 了解记录集,7.1.1 ADO的9个对象,ADO：ActiveX Data Objects Access内嵌的VBA是用ADO技术开发数据库应用的主要工具 ADO对象模型有9个对象： Connection、Recordset、Record、Command、Parameter、Field、Property、
2、Stream、Error 常用对象：Connection、Command、Recordset,7.1.1 ADO的9个对象,Connection对象：ADO对象模型中最高级的对象，实现应用程序与数据源的连接。 Command对象：主要作用是在VBA中通过SQL语句访问、查询数据库中的数据。 Recordset对象：存储访问表和查询对象返回的记录。使用该对象，可以浏览记录、修改记录、添加新的记录或者删除特定的记录。Recordset对象的功能最常用、最重要,7.1.1 ADO的9个对象,ADO的3个对象之间互有联系： Command对象和Recordset对象依赖于Connection对象的连接
3、； Command对象结合SQL命令可以取代Recordset对象，但远没有Recordset对象灵活、实用； Recordset对象它只能实现数据表内记录集操作，无法完成表和数据库的数据定义操作； 数据定义操作一般需通过Command对象用SQL命令完成。本章用DoCmd对象代替Command对象。,7.1.2 了解记录集,记录集(Recordset)：对表执行查询操作时，返回的一组特定记录。 用记录集可执行的操作：对表中的数据进行查询和统计，在表中添加、更新或删除记录。 记录集是一个对象，它包括记录和字段，具有其特定的属性和方法，利用这些属性和方法就可以编程处理数据库中的记录。,7.2 在
4、Access中引用ADO对象,应用程序中的ADO引用：声明Connection对象创建Recordset对象编程完成各种数据访问操作 7.2.1 声明Connection对象 7.2.2 声明与打开Recordset对象 7.2.3 关闭Recordset和Connection对象,7.2.1 声明Connection对象,(1) 声明一个Connection对象 (2) 初始化Connection对象（决定Connection对 象与哪个数据库相连接）例如：Dim cnGraduate As ADODB.ConnectionSet cnGraduate=CurrentProject.Conn
5、ection,7.2.2 声明与打开Recordset对象,(1) 声明Recordset对象 (2) 创建Recordset对象实例 (3) 打开Recordset对象例如：Dim rsStudents As ADODB.RecordsetSet rsStudents=New ADODB.RecordsetrsStudents.Open “研究生“, cnGraduate, , , adCmdTable,7.2.3 关闭Recordset和Connection对象,方法：执行Recordset 对象和Connection对象的Close方法 将对象设置为Nothing 例如： rsStude
6、nts.Close cnGraduate.Close Set rsStudents=Nothing Set cnGraduate=Nothing 上述语句不是必须的。应用程序终止运行时，系统会自动关闭并清除这两个对象。,7.3 引用记录字段,任何对记录集的访问都是针对当前记录进行的。打开记录集时默认的当前记录为第1条记录。 引用记录的字段： 直接在记录集对象中引用字段名称，如Code=rsStudents!学号 若记录集字段名包含空格，或者字段名是一个保留字， 则引用时必须将该字段名用方括号括起来。 使用记录集对象的Fields(n)属性。n是记录中字段从左到右的排列序号，第一个字段的序号为0
7、。如Code=rsStudents.Fields(0),记录集对象与字段名间的连接符,7.3 引用记录字段,例7-1 建立名为ADO的模块，编写如下过程。运行该过程后，输出对话框显示“导师”表中第一位教师的编号和姓名。,7.3 引用记录字段,记录集更多的应用是在窗体对象上：建立一个空白窗体设计各个控件编程引用记录集当前记录的相关字段或将字段的值通过控件显示 注意：如果涉及数据访问的事件过程不止一个，可在代码窗口的通用段定义Connection对象和Recordset对象，然后在Form_Load事件过程中完成数据库连接和数据表的打开。,7.3 引用记录字段,例7-2,运行：分别单击“导师编号”
8、和“导师姓名”按钮,窗体设计视图 (删除导航按钮),7.3 引用记录字段,例7-2窗体程序代码,7.4 浏览记录,Recordset记录集对象提供了4种记录指针的移动方法 MoveFirst 记录指针移到第一条记录 MoveNext 记录指针移到当前记录的下一条记录 MovePrevious 记录指针移到当前记录的上一条记录 MoveLast 记录指针移到最后一条记录 Recordset记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置 记录指针将指向最后一条记录之后，EOF属性为True 记录指针将指向第一条记录之前，BOF属性为True BOF和EOF属性的值均为True，
9、表示记录集为空,7.4 浏览记录,例7-3 对例7-2进行修改,增加如下事件过程代码：Private Sub Command3_Click() 单击“下一个记录”按钮rsTeacher.MoveNextEnd Sub,7.4 浏览记录,上面的过程隐含错误：移至最后一条记录后无法再下移指针，将出现运行错误信息框。 两种纠正错误的方法： 如果记录集的EOF属性为True，就回到第一条记录 如果记录集的EOF属性为True，就回到最后一条记录,Private Sub Command3_Click() 单击“下一个记录”按钮rsTeacher.MoveNext End Sub,7.4 浏览记录,7.4
10、 浏览记录,方法2的程序还将运行出错。因为Recordset对象有一个名为LockType的属性，默认值为adLockReadOnly，此时只能浏览记录数据，记录的移动方式也只能是MoveNext和MoveFirst。 要实现记录指针的4种移动操作，完成记录的添加、删改或更新，必须在使用Open方法打开数据表之前，将该记录集对象的LockType属性设置成adLockPessimistic或adLockOptimistic，如rsTeacher.LockType = adLockPessimisticrsTeacher.Open “导师“, cnGraduate, , , adCmdTable
11、,7.4 浏览记录,Recordset对象的LocyType属性决定数据的锁定方式：adLockReadOnly：数据处于只读状态。 adLockPessimistic(保守式锁定)：编辑数据时即锁定数据源记录，直到数据编辑完成才释放。 adLockOptimistic(开放式锁定)：编辑数据时不锁定，用Update方法提交数据时才锁定数据源记录。 adLockBatchOptimistic(开放式更新)：应用于批更新模式,7.4 浏览记录,如果数据表中没有记录，即BOF和EOF同时为True，就不能引用表中的数据。 将单击“导师姓名”按钮的事件过程改写如下 Private Sub Comma
12、nd2_Click()If rsTeacher.BOF = True And rsTeacher.EOF = True ThenText1.Value = “ElseText1.Value = rsTeacher!姓名End If End Sub 同理，修改单击“导师编号”按钮的事件过程 同理，修改“下一个记录”按钮的事件过程代码(空表情况下记录指针不移动),7.5 编辑数据,7.5.1 用ADO记录集的AddNew方法添加记录 7.5.2 用ADO记录集的Update方法修改记录 7.5.3 用ADO记录集的Delete方法删除记录,7.5.1 用ADO记录集的AddNew方法添加记录,添加
13、新记录的步骤： （1）用记录集的AddNew方法产生一个空记录 （2）为空记录的各个字段赋值 （3）用记录集的Update方法保存新记录 例7-4 在例7-3的基础上进行修改,7.5.1 用ADO记录集的AddNew方法添加记录,添加的程序代码:,Private Sub Command4_Click()Dim Age As BytersTeacher.MoveFirstAge = rsTeacher!年龄 读取第一条记录的年龄字段值rsTeacher.AddNew 添加一条新记录rsTeacher!导师编号 = “107“rsTeacher!姓名 = “高原“rsTeacher!年龄 = Ag
14、ersTeacher.Update End Sub,思考：什么情况下单击“新记录”按钮，系统会提示出错 ？,7.5.2 用ADO记录集的Update方法修改记录,修改记录的步骤： （1）将记录指针移动到需要修改的记录上 （2）对记录中的相关字段的值进行修改 （3）用Update方法保存更改 修改后的结果不得违反数据完整性约束 例7-5 在模块ADO中添加过程Update_Age：将 “导师”表中第5条记录的年龄字段值修改为60岁。,注意设置记录集的LockType类型,7.5.2 用ADO记录集的Update方法修改记录,Sub Update_Age()Dim I As ByteDim cnG
15、raduate As ADODB.ConnectionSet cnGraduate = CurrentProject.ConnectionDim rsTeacher As ADODB.RecordsetSet rsTeacher = New ADODB.RecordsetrsTeacher.LockType = adLockPessimisticrsTeacher.Open “导师“, cnGraduate, , , adCmdTable 向下跳过4条记录，将记录指针指向第5条记录For I = 1 To 4rsTeacher.MoveNextNext IrsTeacher!年龄 = 60 :
16、 rsTeacher.Update End Sub,7.5.2 用ADO记录集的Update方法修改记录,例7-6在例7-4的基础上进行修改 添加的程序代码:,Private Sub Command5_Click()rsTeacher.MoveFirstDo While Not rsTeacher.EOFIf rsTeacher!性别 = “男“ ThenrsTeacher!年龄 = rsTeacher!年龄 + 1rsTeacher.UpdateEnd IfrsTeacher.MoveNextLoop End Sub,7.5.3 用ADO记录集的Delete方法删除记录,删除记录要慎重，因为
17、被删记录无法恢复 删除记录的步骤: （1）移动到需要删除的记录上 （2）用记录集对象的Delete方法删除当前记录 （3）将某条记录指定为当前记录 删除记录后，Access不能自动使下一条记录成为当前记录。根据不同情况，可用MoveNext或MoveLast方法定位记录指针。,7.5.3 用ADO记录集的Delete方法删除记录,例7-7 对例7-6添加如下的单击窗体事件过程：,7.5.3 用ADO记录集的Delete方法删除记录,If rsTeacher!姓名 = “高原“ ThenFlag = MsgBox(“是否要删除高原？“, vbYesNo, “删除确认“)If Flag = vbY
18、es Then 确定删除rsTeacher.DeleteMsgBox “记录删除完毕。“rsTeacher.MoveNext 删除记录后设定新的当前记录 If rsTeacher.EOF Then rsTeacher.MoveLastExit SubElseIf Flag = vbNo Then 不删除记录MsgBox “放弃删除操作！“, , “删除确认“Exit SubEnd If End If,如果找到“高原”，进行相关操作，然后退出过程,7.6 用ADO技术实现复杂查询,例7-8 在ADO模块中建立Sex过程：统计并在输出对话框中显示男、女研究生人数比。要求以人数多的一方为1、放在右侧
19、、保留小数两位。,程序分析：先遍历全部记录，分别统计出男、女研究生人数。如果男生人数多于女生，则男生人数为1，女生人数改为女生人数除以男生人数；反之亦然。,7.6 用ADO技术实现复杂查询,Sub Sex() （定义对象、完成与数据库的连接、打开研究生表、定义变量）Student.MoveFirstDo While Not Student.EOF 统计男、女研究生人数If Student!性别 = “男“ Then Boy = Boy + 1If Student!性别 = “女“ Then Girl = Girl + 1Student.MoveNextLoopIf Girl = Boy The
20、n 以男生人数为1MsgBox “女:男=“ & Format(Girl / Boy, “0.00“) & “:1“Else 以女生人数为1MsgBox “男:女=“ & Format(Boy / Girl, “0.00“) & “:1“End If End Sub,7.6 用ADO技术实现复杂查询,例7-9 略 例7-10,7.6 用ADO技术实现复杂查询,Private Sub Form_Load()MSFlexGrid1.Rows = 1MSFlexGrid1.Cols = 8以下为MsFlexGrid添加列标题MSFlexGrid1.Row = 0: MSFlexGrid1.Col =
21、 1 选定0行1列MSFlexGrid1.Value = “学号“MSFlexGrid1.Col = 2: MSFlexGrid1.Value = “姓名“MSFlexGrid1.Col = 3: MSFlexGrid1.Value = “性别“MSFlexGrid1.Col = 4: MSFlexGrid1.Value = “入学日期“MSFlexGrid1.Col = 5: MSFlexGrid1.Value = “入学分数“MSFlexGrid1.Col = 6: MSFlexGrid1.Value = “研究方向“MSFlexGrid1.Col = 7: MSFlexGrid1.Val
22、ue = “导师编号“ End Sub,7.6 用ADO技术实现复杂查询,Private Sub Command1_Click() 单击“生成“按钮 （定义变量、定义对象、完成与数据库的连接、打开研究生表）Student.MoveFirstDo While Not Student.EOFFor I = 2 To Student!入学分数 - 1If Student!入学分数 Mod I = 0 Then Exit For 不是质数Next IIf I = Student!入学分数 Then 如果入学分数为质数S = “For I = 0 To 6 将当前记录各个字段连成一行 S = S & S
23、tudent.Fields(I) & Chr(9)Next IMSFlexGrid1.AddItem “” & vbTab & S 跳过每行左侧的标题栏End IfStudent.MoveNextLoop End Sub,7.7 在VBA程序中使用SQL命令,Access提供了DoCmd对象，该对象的RunSQL方法可以在VBA程序中用SQL命令直接对数据源进行操作。 RunSQL方法的格式为： DoCmd.RunSQL 也可以 Dim As String= DoCmd.RunSQL 是一对由双引号括起来的SQL命令,7.7 在VBA程序中使用SQL命令,7.7.1 定义数据 7.7.2 编辑
24、数据 7.7.3 实现数据完整性约束 7.7.4 执行查询操作,7.7.1 定义数据,1. 创建数据表 CREATE TABLE (字段名 数据类型 NULL | NOT NULL | 字段名 AS 计算表达式, .n)例7-11 在SQL模块中建立如下过程：,7.7.1 定义数据,2. 在表中增加字段 例7-12 为Student表增加一个货币型的字段“学费”3. 改变字段的类型 例7-13 修改Student表中“年龄”字段的类型,Sub Add_Field()DoCmd.RunSQL “ALTER TABLE Student ADD 学费 CURRENCY“ End Sub,7.7.1
25、定义数据,4. 改变字段的宽度 例7-14 更改Student表中“姓名”字段的宽度 5. 删除一个字段 例7-15 删除Student表中的“年龄”字段,Sub Delete_Field()DoCmd.RunSQL “ALTER TABLE Student DROP 年龄“ End Sub,7.7.1 定义数据,6. 删除一个表 例7-16 删除Student表 7. 修改数据表名字 例7-17 将Student表的名字更改成“学生”,Sub Rename_Table()DoCmd.Rename “学生“, acTable, “Student“ End Sub,Sub Delete_Tabl
26、e()DoCmd.RunSQL “DROP TABLE Student“ End Sub,7.7.2 编辑数据,1. 向表中追加记录例7-18 在Student表中添加一条记录,Sub Insert_Table()DoCmd.RunSQL “INSERT INTO Student VALUES(李大明, 35, 2003-1-15)“ End Sub,VALUES后的数据与表中字段的顺序要一一对应,说明： （1）日期型常量可以放在一对单引号或一对#中。 （2）如果数据是通过输入对话框等途径获得，需在SQL命令中使用变量，然后用&运算符将变量连接到SQL命令中。对于字符串变量或日期型变量，要在这
27、些变量的两侧加上一对单引号。,7.7.2 编辑数据,例7-19 通过变量为Student表添加一条记录,Sub Insert_Table_VBA()Dim S_name As StringDim Age As Byte, S_date As DateS_name = InputBox(“输入学生姓名：“)S_date = InputBox(“入学日期：“)Age = 21DoCmd.RunSQL “INSERT INTO Student VALUES(“ & S_name & “,“ & Age & “,“ & S_date & “)“ End Sub,注意单引号和双引号出现的位置,7.7.2
28、 编辑数据,2. 修改表中记录 例7-20 将“导师”表中“李向明”的年龄改成40 例7-21 将“导师”表中所有男导师的年龄增加1岁,Sub Update_Table_1()DoCmd.RunSQL “UPDATE 导师 SET 年龄=40 WHERE 姓名=李向明“ End Sub,如果本例用ADO技术编程，代码会怎样 ？,Sub Update_Table_2()DoCmd.RunSQL “UPDATE 导师 SET 年龄=年龄+1 WHERE 性别=男“ End Sub,7.7.2 编辑数据,3. 删除特定记录 例7-22 将“导师”表中年龄在50岁以下的记录全部删除,Sub Delet
29、e_Record()DoCmd.RunSQL “DELETE FROM 导师 WHERE 年龄50“ End Sub,思考： （1）如果要求将“导师”表中低于年龄平均值的导师记录删除，例7-22应怎样修改？ （2）如果要删除小于X岁的所有记录，X的值通过键盘在程序运行时输入，程序又该怎样修改？,7.7.3 实现数据完整性约束,1. 设置主键 例7-23 为“导师”表和“研究生”表设置主键 （如果这两个表已有主键和外键，请先予以删除）例7-24 在创建新表Teacher 的同时设定code为主键,Sub Create_Primary()DoCmd.RunSQL “Alter Table 导师 A
30、dd Primary Key (导师编号)“DoCmd.RunSQL “Alter Table 研究生 Add Primary Key (学号)“ End Sub,Sub Create_Table_Primary()DoCmd.RunSQL “CREATE TABLE Teacher (code text(3) PRIMARY KEY, name text(6), birthday date, salary currency)“ End Sub,7.7.3 实现数据完整性约束,2. 设置外键 例7-25 将研究生表中的“导师编号”设为外键，对应的参照表是“导师”表(建立“导师”表和“研究生”表
31、间的关系)例7-26 在创建新表Student1的同时指定其外键和参照表,Sub Create_Foreign()DoCmd.RunSQL “Alter Table 研究生 Add Foreign Key (导师编号) References 导师)“ End Sub,7.7.4 执行查询操作,VBA程序中用SQL命令完成的数据查询操作，无法直接将查询结果所返回的记录集按数据表形式显示，解决方法： 方法1：将查询形成的记录集生成一个新表保存到数据库中；然后用ADO记录集对象打开这个表进行各种操作；完成后删除这个表。 方法2：将返回的记录集看成是保存在内存中的一个临时表，用ADO记录集对象直接打开
32、该临时表进行各种操作。,7.7.4 执行查询操作,例7-27,7.7.4 执行查询操作,例7-28,Sub Query2()Dim SQL As StringSQL = “SELECT t.姓名 as 导师姓名,s.姓名 as 学生姓名 FROM 导师 t,研究生 s WHERE t.导师编号=s.导师编号 and t.性别=男“(定义对象、连接数据库、设置记录集LockType属性、打开temp表)rsTeacher.Open SQL, cnGraduate 打开查询记录集Do While Not rsTeacher.EOFDebug.Print rsTeacher!导师姓名, rsTeac
33、her!学生姓名rsTeacher.MoveNextLoop End Sub,t、s分别作为“导师”表和“研究生”表的别名,例7-29 不使用SQL命令实现上题的功能。略,7.8 访问当前数据库以外的数据库,连接另一个数据库：使用Connection对象的Open方法。语法格式为：.Open “Provider=;Data Source=;User ID=用户标识;Password=密码;“其中： 提供者为Microsoft.Jet.OLEDB.4.0 数据库名包括数据库所在的路径及.mdb文件的名字 用户标识是用户的名字，缺省为admin 密码省略则表示没有密码,7.8 访问当前数据库以外的
34、数据库,例7-30 在SQL模块中编写过程Double_Database （Department表保存在C:Manage.mdb数据库中）,Sub Double_Database()(定义对象、连接数据库、设置记录集LockType属性、打开“导师“表) 建立与另一个数据库Department的连接Dim Dept As ADODB.ConnectionSet Dept = New ADODB.ConnectionDept.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Manage.mdb;User ID=;Password=;“,转
35、下页,7.8 访问当前数据库以外的数据库,访问Departmrnt表Dim Unit As ADODB.RecordsetSet Unit = New ADODB.RecordsetUnit.LockType = adLockOptimisticUnit.Open “Department“, Dept, , , adCmdTable输入导师姓名Dim Teacher As StringTeacher = InputBox(“输入导师姓名“, “导师名字“)遍历当前表(导师表)，寻找该导师rsTeacher.MoveFirstDo While Not rsTeacher.EOF If rsTea
36、cher!姓名 = Teacher ThenExit DoEnd IfrsTeacher.MoveNextLoop,转下页,接 上 页,找到，则提前退出循环,7.8 访问当前数据库以外的数据库,正常退出上面的循环，表示没找到该导师If rsTeacher.EOF ThenMsgBox “未找到“ & Teacher & “！“Exit Sub End IfUnit.MoveFirstDo While Not Unit.EOFIf Unit!系编号 = rsTeacher!系编号 ThenMsgBox rsTeacher!姓名 & “ “ & Unit!系名Exit SubEnd IfUnit.MoveNextLoop End Sub,接 上 页,没找到，则提前退出过程,7.9 综合实例编制“研究生成绩管理与统计”程序,
