数据库编程ADO详细PPT课件

1、本章内容 第1页/共57页7.1 记录集概述 第2页/共57页7.1.1 ADO的9个对象 ADO：ActiveX Data Objects Access内嵌的VBA是用ADO技术开发数据库应用的主要工具 ADO对象模型有9个对象： Connection、Recordset、Record、Command、Parameter、Field、Property、Stream、Error 常用对象：Connection、Command、Recordset第3页/共57页7.1.1 ADO的9个对象 Connection对象：ADO对象模型中最高级的对象，实现应用程序与数据源的连接。 Command对象：

2、主要作用是在VBA中通过SQL语句访问、查询数据库中的数据。 Recordset对象：存储访问表和查询对象返回的记录。使用该对象，可以浏览记录、修改记录、添加新的记录或者删除特定的记录。 Recordset对象的功能最常用、最重要第4页/共57页7.1.1 ADO的9个对象 ADO的3个对象之间互有联系： Command对象和Recordset对象依赖于Connection对象的连接； Command对象结合SQL命令可以取代Recordset对象，但远没有Recordset对象灵活、实用； Recordset对象它只能实现数据表内记录集操作，无法完成表和数据库的数据定义操作； 数据定义操作一

3、般需通过Command对象用SQL命令完成。本章用DoCmd对象代替Command对象。 第5页/共57页7.1.2 了解记录集 记录集(Recordset)：对表执行查询操作时，返回的一组特定记录。 用记录集可执行的操作：对表中的数据进行查询和统计，在表中添加、更新或删除记录。 记录集是一个对象，它包括记录和字段，具有其特定的属性和方法，利用这些属性和方法就可以编程处理数据库中的记录。第6页/共57页7.2 在Access中引用ADO对象 应用程序中的ADO引用：声明Connection对象创建Recordset对象编程完成各种数据访问操作第7页/共57页7.2.1 声明Connection

4、对象 (1) 声明一个Connection对象 (2) 初始化Connection对象（决定Connection对 象与哪个数据库相连接） 例如： Dim cnGraduate As ADODB.Connection Set cnGraduate=CurrentProject.Connection 与当前数据库连接 第8页/共57页7.2.2 声明与打开Recordset对象 (1) 声明Recordset对象 (2) 创建Recordset对象实例 (3) 打开Recordset对象 例如： Dim rsStudents As ADODB.Recordset Set rsStudents=N

5、ew ADODB.Recordset rsStudents.Open 研究生, cnGraduate, , , adCmdTable 前面声明的Connection对象 表名称 打开的是表对象 参数占位符 第9页/共57页7.2.3 关闭Recordset和Connection对象 方法：执行Recordset 对象和Connection对象的Close方法 将对象设置为Nothing例如：rsStudents.ClosecnGraduate.CloseSet rsStudents=NothingSet cnGraduate=Nothing 上述语句不是必须的。应用程序终止运行时，系统会自动关

6、闭并清除这两个对象。 第10页/共57页7.3 引用记录字段 任何对记录集的访问都是针对当前记录进行的。打开记录集时默认的当前记录为第1条记录。 引用记录的字段： 直接在记录集对象中引用字段名称，如 Code=rsStudents!学号若记录集字段名包含空格，或者字段名是一个保留字，则引用时必须将该字段名用方括号括起来。 使用记录集对象的Fields(n)属性。n是记录中字段从左到右的排列序号，第一个字段的序号为0。如 Code=rsStudents.Fields(0)记录集对象与字段名间的连接符 第11页/共57页7.3 引用记录字段 例7-1 建立名为ADO的模块，编写如下过程。运行该过程

7、后，输出对话框显示“导师”表中第一位教师的编号和姓名。 第12页/共57页7.3 引用记录字段 记录集更多的应用是在窗体对象上：建立一个空白窗体设计各个控件编程引用记录集当前记录的相关字段或将字段的值通过控件显示 注意：如果涉及数据访问的事件过程不止一个，可在代码窗口的通用段定义Connection对象和Recordset对象，然后在Form_Load事件过程中完成数据库连接和数据表的打开。第13页/共57页7.3 引用记录字段 例7-2 运行：分别单击“导师编号”和“导师姓名”按钮 窗体设计视图(删除导航按钮) 第14页/共57页7.3 引用记录字段 例7-2窗体程序代码 第15页/共57页

8、7.4 浏览记录 Recordset记录集对象提供了4种记录指针的移动方法 MoveFirst 记录指针移到第一条记录 MoveNext 记录指针移到当前记录的下一条记录 MovePrevious 记录指针移到当前记录的上一条记录 MoveLast 记录指针移到最后一条记录 Recordset记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置 记录指针将指向最后一条记录之后，EOF属性为True 记录指针将指向第一条记录之前，BOF属性为True BOF和EOF属性的值均为True，表示记录集为空 第16页/共57页7.4 浏览记录 例7-3 对例7-2进行修改 增加如下事件过

9、程代码： Private Sub Command3_Click() 单击“下一个记录”按钮 rsTeacher.MoveNext End Sub第17页/共57页7.4 浏览记录 上面的过程隐含错误：移至最后一条记录后无法再下移指针，将出现运行错误信息框。 两种纠正错误的方法： 如果记录集的EOF属性为True，就回到第一条记录 如果记录集的EOF属性为True，就回到最后一条记录 Private Sub Command3_Click() 单击“下一个记录”按钮 rsTeacher.MoveNextEnd Sub第18页/共57页7.4 浏览记录 Private Sub Command3_Cl

10、ick() rsTeacher.MoveNext If rsTeacher.EOF Then rsTeacher.MoveFirst End IfEnd Sub 方法1：若记录集的EOF属性为True，则回到首记录 方法2：若记录集的EOF属性为True，则回到末记录Private Sub Command3_Click() rsTeacher.MoveNext If rsTeacher.EOF Then rsTeacher. MoveLast End IfEnd Sub第19页/共57页7.4 浏览记录 方法2的程序还将运行出错。因为Recordset对象有一个名为LockType的属性，默认

11、值为adLockReadOnly，此时只能浏览记录数据，记录的移动方式也只能是MoveNext和MoveFirst。 要实现记录指针的4种移动操作，完成记录的添加、删改或更新，必须在使用Open方法打开数据表之前，将该记录集对象的LockType属性设置成adLockPessimistic或adLockOptimistic，如 rsTeacher.LockType = adLockPessimistic rsTeacher.Open 导师, cnGraduate, , , adCmdTable第20页/共57页7.4 浏览记录 Recordset对象的LocyType属性决定数据的锁定方式：

12、adLockReadOnly：数据处于只读状态。 adLockPessimistic(保守式锁定)：编辑数据时即锁定数据源记录，直到数据编辑完成才释放。 adLockOptimistic(开放式锁定)：编辑数据时不锁定，用Update方法提交数据时才锁定数据源记录。 adLockBatchOptimistic(开放式更新)：应用于批更新模式 第21页/共57页7.4 浏览记录 如果数据表中没有记录，即BOF和EOF同时为True，就不能引用表中的数据。 将单击“导师姓名”按钮的事件过程改写如下Private Sub Command2_Click() If rsTeacher.BOF = Tru

13、e And rsTeacher.EOF = True Then Text1.Value = Else Text1.Value = rsTeacher!姓名 End IfEnd Sub 同理，修改单击“导师编号”按钮的事件过程 同理，修改“下一个记录”按钮的事件过程代码(空表情况下记录指针不移动) 第22页/共57页7.5 编辑数据 第23页/共57页7.5.1 用ADO记录集的AddNew方法添加记录添加新记录的步骤：（1）用记录集的AddNew方法产生一个空记录（2）为空记录的各个字段赋值（3）用记录集的Update方法保存新记录例7-4 在例7-3的基础上进行修改 第24页/共57页7.5

14、.1 用ADO记录集的AddNew方法添加记录添加的程序代码:Private Sub Command4_Click() Dim Age As Byte rsTeacher.MoveFirst Age = rsTeacher!年龄 读取第一条记录的年龄字段值 rsTeacher.AddNew 添加一条新记录 rsTeacher!导师编号 = 107 rsTeacher!姓名 = 高原 rsTeacher!年龄 = Age rsTeacher.UpdateEnd Sub思考：什么情况下单击“新记录”按钮，系统会提示出错 ？第25页/共57页7.5.2 用ADO记录集的Update方法修改记录 修改

15、记录的步骤：（1）将记录指针移动到需要修改的记录上（2）对记录中的相关字段的值进行修改（3）用Update方法保存更改 修改后的结果不得违反数据完整性约束 例7-5 在模块ADO中添加过程Update_Age：将“导师”表中第5条记录的年龄字段值修改为60岁。注意设置记录集的LockType类型第26页/共57页7.5.2 用ADO记录集的Update方法修改记录Sub Update_Age() Dim I As Byte Dim cnGraduate As ADODB.Connection Set cnGraduate = CurrentProject.Connection Dim rsTe

16、acher As ADODB.Recordset Set rsTeacher = New ADODB.Recordset rsTeacher.LockType = adLockPessimistic rsTeacher.Open 导师, cnGraduate, , , adCmdTable 向下跳过4条记录，将记录指针指向第5条记录 For I = 1 To 4 rsTeacher.MoveNext Next I rsTeacher!年龄 = 60 : rsTeacher.UpdateEnd Sub第27页/共57页7.5.2 用ADO记录集的Update方法修改记录 例7-6在例7-4的基础

17、上进行修改添加的程序代码: Private Sub Command5_Click() rsTeacher.MoveFirst Do While Not rsTeacher.EOF If rsTeacher!性别 = 男 Then rsTeacher!年龄 = rsTeacher!年龄 + 1 rsTeacher.Update End If rsTeacher.MoveNext LoopEnd Sub第28页/共57页7.5.3 用ADO记录集的Delete方法删除记录 删除记录要慎重，因为被删记录无法恢复 删除记录的步骤:（1）移动到需要删除的记录上（2）用记录集对象的Delete方法删除当前

18、记录（3）将某条记录指定为当前记录 删除记录后，Access不能自动使下一条记录成为当前记录。根据不同情况，可用MoveNext或MoveLast方法定位记录指针。 第29页/共57页7.5.3 用ADO记录集的Delete方法删除记录 例7-7 对例7-6添加如下的单击窗体事件过程：Private Sub Form_Click() rsTeacher.MoveFirst Do While Not rsTeacher.EOF rsTeacher.MoveNext Loop MsgBox 待删除记录不存在！End Sub如果找到“高原”,进行相关操作，然后退出过程转下页第30页/共57页7.5.

19、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 放弃删除操作！, , 删除确认 Exit Sub End IfEnd

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

21、研究生人数 If Student!性别 = 男 Then Boy = Boy + 1 If Student!性别 = 女 Then Girl = Girl + 1 Student.MoveNext Loop If Girl = Boy Then 以男生人数为1 MsgBox 女:男= & Format(Girl / Boy, 0.00) & :1 Else 以女生人数为1 MsgBox 男:女= & Format(Boy / Girl, 0.00) & :1 End IfEnd Sub第33页/共57页7.6 用ADO技术实现复杂查询 例7-9 略例7-10第3

22、4页/共57页7.6 用ADO技术实现复杂查询 Private Sub Form_Load() MSFlexGrid1.Rows = 1 MSFlexGrid1.Cols = 8 以下为MsFlexGrid添加列标题 MSFlexGrid1.Row = 0: MSFlexGrid1.Col = 1 选定0行1列 MSFlexGrid1.Value = 学号 MSFlexGrid1.Col = 2: MSFlexGrid1.Value = 姓名 MSFlexGrid1.Col = 3: MSFlexGrid1.Value = 性别 MSFlexGrid1.Col = 4: MSFlexGrid1

23、.Value = 入学日期 MSFlexGrid1.Col = 5: MSFlexGrid1.Value = 入学分数 MSFlexGrid1.Col = 6: MSFlexGrid1.Value = 研究方向 MSFlexGrid1.Col = 7: MSFlexGrid1.Value = 导师编号End Sub第35页/共57页7.6 用ADO技术实现复杂查询 Private Sub Command1_Click() 单击生成按钮（定义变量、定义对象、完成与数据库的连接、打开研究生表） Student.MoveFirst Do While Not Student.EOF For I = 2

24、 To Student!入学分数 - 1 If Student!入学分数 Mod I = 0 Then Exit For 不是质数 Next I If I = Student!入学分数 Then 如果入学分数为质数 S = For I = 0 To 6 将当前记录各个字段连成一行 S = S & Student.Fields(I) & Chr(9) Next I MSFlexGrid1.AddItem “” & vbTab & S 跳过每行左侧的标题栏 End If Student.MoveNext LoopEnd Sub第36页/共57页7.7 在VBA程序中

25、使用SQL命令 Access提供了DoCmd对象，该对象的RunSQL方法可以在VBA程序中用SQL命令直接对数据源进行操作。 RunSQL方法的格式为：DoCmd.RunSQL 也可以Dim As String =DoCmd.RunSQL 是一对由双引号括起来的SQL命令第37页/共57页7.7 在VBA程序中使用SQL命令 第38页/共57页7.7.1 定义数据1. 创建数据表 CREATE TABLE (字段名 数据类型 NULL | NOT NULL | 字段名 AS 计算表达式, .n) 例7-11 在SQL模块中建立如下过程： Sub Create_Table() DoCmd.Ru

26、nSQL CREATE TABLE Student (姓名 text(6), 年龄 byte, 入学日期 date)End SubSub Create_Table() Dim Sql As String Sql = CREATE TABLE Student (姓名 text(6), 年龄 byte, 入学日期 date) DoCmd.RunSQL SqlEnd Sub等价第39页/共57页7.7.1 定义数据2. 在表中增加字段 例7-12 为Student表增加一个货币型的字段“学费” 3. 改变字段的类型 例7-13 修改Student表中“年龄”字段的类型Sub Add_Field()

27、DoCmd.RunSQL ALTER TABLE Student ADD 学费 CURRENCYEnd SubSub Alter_Fields_Type() DoCmd.RunSQL ALTER TABLE Student ALTER 年龄 SmallIntEnd Sub如果字段的新类型与原类型不兼容将造成数据丢失 第40页/共57页7.7.1 定义数据4. 改变字段的宽度 例7-14 更改Student表中“姓名”字段的宽度 5. 删除一个字段 例7-15 删除Student表中的“年龄”字段 Sub Delete_Field() DoCmd.RunSQL ALTER TABLE Stude

28、nt DROP 年龄End SubSub Alter_Fields_Width() DoCmd.RunSQL ALTER TABLE Student ALTER 姓名 text(10)End Sub字段的宽度由大变小，有可能丢失数据 第41页/共57页7.7.1 定义数据6. 删除一个表 例7-16 删除Student表 7. 修改数据表名字 例7-17 将Student表的名字更改成“学生” Sub Rename_Table() DoCmd.Rename 学生, acTable, StudentEnd SubSub Delete_Table() DoCmd.RunSQL DROP TABLE

29、 StudentEnd Sub第42页/共57页7.7.2 编辑数据1. 向表中追加记录 例7-18 在Student表中添加一条记录Sub Insert_Table() DoCmd.RunSQL INSERT INTO Student VALUES(李大明, 35, 2003-1-15)End SubVALUES后的数据与表中字段的顺序要一一对应 说明：（1）日期型常量可以放在一对单引号或一对#中。（2）如果数据是通过输入对话框等途径获得，需在SQL命令中使用变量，然后用&运算符将变量连接到SQL命令中。对于字符串变量或日期型变量，要在这些变量的两侧加上一对单引号。第43页/共57页

30、7.7.2 编辑数据 例7-19 通过变量为Student表添加一条记录 Sub Insert_Table_VBA() Dim S_name As String Dim Age As Byte, S_date As Date S_name = InputBox(输入学生姓名：) S_date = InputBox(入学日期：) Age = 21 DoCmd.RunSQL INSERT INTO Student VALUES( & S_name & , & Age & , & S_date & )End Sub注意单引号和双引号出现的位置 第44页

31、/共57页7.7.2 编辑数据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第45页/共57页7.7.2 编辑数据3. 删除特定记录 例7-22 将“导师”表中年龄在50岁以下的记录全部删除

32、 Sub Delete_Record() DoCmd.RunSQL DELETE FROM 导师 WHERE 年龄50End Sub思考：（1）如果要求将“导师”表中低于年龄平均值的导师记录删除，例7-22应怎样修改？（2）如果要删除小于X岁的所有记录，X的值通过键盘在程序运行时输入，程序又该怎样修改？ 第46页/共57页7.7.3 实现数据完整性约束 1. 设置主键例7-23 为“导师”表和“研究生”表设置主键（如果这两个表已有主键和外键，请先予以删除）例7-24 在创建新表Teacher 的同时设定code为主键 Sub Create_Primary() DoCmd.RunSQL Alte

33、r Table 导师 Add Primary Key (导师编号) DoCmd.RunSQL Alter Table 研究生 Add Primary Key (学号)End SubSub Create_Table_Primary() DoCmd.RunSQL CREATE TABLE Teacher (code text(3) PRIMARY KEY, name text(6), birthday date, salary currency)End Sub第47页/共57页7.7.3 实现数据完整性约束 2. 设置外键例7-25 将研究生表中的“导师编号”设为外键，对应的参照表是“导师”表(建

34、立“导师”表和“研究生”表间的关系)例7-26 在创建新表Student1的同时指定其外键和参照表Sub Create_Foreign() DoCmd.RunSQL Alter Table 研究生 Add Foreign Key (导师编号) References 导师)End SubSub Create_Table_Foreign() DoCmd.RunSQL Create Table Student1 (code text(4) Primary Key, name text(6), sex bit, t_code text(3), Foreign Key (t_code) Referenc

35、es Teacher (code)End Subbit表示“是/否”型变量 第48页/共57页7.7.4 执行查询操作 VBA程序中用SQL命令完成的数据查询操作，无法直接将查询结果所返回的记录集按数据表形式显示，解决方法： 方法1：将查询形成的记录集生成一个新表保存到数据库中；然后用ADO记录集对象打开这个表进行各种操作；完成后删除这个表。 方法2：将返回的记录集看成是保存在内存中的一个临时表，用ADO记录集对象直接打开该临时表进行各种操作。第49页/共57页7.7.4 执行查询操作例7-27 Sub Query1() DoCmd.RunSQL Select 姓名, 职称, 年龄 Into

36、temp From 导师 Where 职称 in (教授,副教授) (定义对象、连接数据库、设置记录集LockType属性、打开temp表) Do While Not rsTeacher.EOF Debug.Print rsTeacher!姓名, rsTeacher!职称, rsTeacher!年龄 rsTeacher.MoveNext Loop rsTeacher.Close 关闭记录集对象，终止对temp的引用 DoCmd.RunSQL Drop Table temp 删除数据表tempEnd Sub删除temp表之前必须先关闭对表的引用 职称 =教授 Or 职称 =副教授 第50页/共5

37、7页7.7.4 执行查询操作例7-28 Sub Query2() Dim SQL As String SQL = SELECT t.姓名 as 导师姓名,s.姓名 as 学生姓名 FROM 导师 t,研究生 s WHERE t.导师编号=s.导师编号 and t.性别=男 (定义对象、连接数据库、设置记录集LockType属性、打开temp表) rsTeacher.Open SQL, cnGraduate 打开查询记录集 Do While Not rsTeacher.EOF Debug.Print rsTeacher!导师姓名, rsTeacher!学生姓名 rsTeacher.MoveNext LoopEnd Subt、s分别作为“导师”表和“研究生”表的别名 例7-29 不使用SQL命令实现上题的功能。略第51页/共57页7.8 访问当前数