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

Access-VBAAccess-VBA数据库编程数据库编程(ADO)-(ADO)-详细详细本章内容本章内容n n 记录集记录集记录集记录集概述概述概述概述n n 在在在在AccessAccess中引用中引用中引用中引用ADOADO对象对象对象对象 n n 引用记录字段引用记录字段引用记录字段引用记录字段 n n 浏览记录浏览记录浏览记录浏览记录 n n 编辑数据编辑数据编辑数据编辑数据 n n 用用用用ADOADO技术实现复杂查询技术实现复杂查询技术实现复杂查询技术实现复杂查询n n 在在在在VBAVBA程序中使用程序中使用程序中使用程序中使用SQLSQL命令命令命令命令n n 访问当前数据库以外的数据库访问当前数据库以外的数据库访问当前数据库以外的数据库访问当前数据库以外的数据库n n 综合实例综合实例综合实例综合实例编制编制编制编制“研究生成绩管理与统计研究生成绩管理与统计研究生成绩管理与统计研究生成绩管理与统计”程序程序程序程序7.1 7.1 记录集概述记录集概述 7.1.1 ADO的的9个对象个对象7.1.2 了解记录集了解记录集 7.1.1 7.1.1 ADO的的9个对象个对象 nADO：ActiveX Data Objects nAccess内嵌的内嵌的VBA是用是用ADO技术开发数据库应技术开发数据库应用的主要工具用的主要工具nADO对象模型有对象模型有9个对象：个对象：Connection、Recordset、Record、Command、Parameter、Field、Property、Stream、Error 常用对象：常用对象：Connection、Command、Recordset7.1.1 7.1.1 ADO的的9个对象个对象 nConnection对象：对象：ADO对象模型中最高级的对对象模型中最高级的对象，实现应用程序与数据源的连接。象，实现应用程序与数据源的连接。nCommand对象：主要作用是在对象：主要作用是在VBA中通过中通过SQL语句访问、查询数据库中的数据。语句访问、查询数据库中的数据。nRecordset对象：存储访问表和查询对象返回的对象：存储访问表和查询对象返回的记录。使用该对象，可以浏览记录、修改记录、记录。使用该对象，可以浏览记录、修改记录、添加新的记录或者删除特定的记录。添加新的记录或者删除特定的记录。Recordset对象的功能最常用、最重要对象的功能最常用、最重要7.1.1 7.1.1 ADO的的9个对象个对象 nADO的的3个对象之间互有联系：个对象之间互有联系：Command对象和对象和Recordset对象依赖于对象依赖于Connection对象的连接；对象的连接；Command对象结合对象结合SQL命令可以取代命令可以取代Recordset对对象，但远没有象，但远没有Recordset对象灵活、实用；对象灵活、实用；Recordset对象它只能实现数据表内记录集操作，无对象它只能实现数据表内记录集操作，无法完成表和数据库的数据定义操作；法完成表和数据库的数据定义操作；数据定义操作一般需通过数据定义操作一般需通过Command对象用对象用SQL命令命令完成。本章用完成。本章用DoCmd对象代替对象代替Command对象对象。7.1.2 7.1.2 了解记录集了解记录集 n记录集记录集(Recordset)：对表执行查询操作时，返：对表执行查询操作时，返回的一组特定记录。回的一组特定记录。n用记录集可执行的操作：对表中的数据进行查询用记录集可执行的操作：对表中的数据进行查询和统计，在表中添加、更新或删除记录。和统计，在表中添加、更新或删除记录。n记录集是一个对象，它包括记录和字段，具有其记录集是一个对象，它包括记录和字段，具有其特定的属性和方法，利用这些属性和方法就可以特定的属性和方法，利用这些属性和方法就可以编程处理数据库中的记录。编程处理数据库中的记录。7.2 7.2 在在Access中引用中引用ADO对象对象 n应用程序中的应用程序中的ADO引用：声明引用：声明Connection对象对象创建创建Recordset对象对象编程完成各种数据访问编程完成各种数据访问操作操作7.2.1 声明声明Connection对象对象7.2.2 声明与打开声明与打开Recordset对对象象7.2.3 关关闭闭Recordset和和Connection对对象象7.2.1 7.2.1 声明声明Connection对象对象 (1)声明一个声明一个Connection对象对象(2)初始化初始化Connection对象（决定对象（决定Connection对对 象与哪个数据库相连接）象与哪个数据库相连接）例如：例如：Dim cnGraduate As ADODB.Connection Set cnGraduate=CurrentProject.Connection 与当前数据库连接与当前数据库连接 7.2.2 7.2.2 声明声明与打开与打开Recordset对象对象 (1)声明声明Recordset对象对象(2)创建创建Recordset对象实例对象实例(3)打开打开Recordset对象对象 例如：例如：Dim rsStudents As ADODB.Recordset Set rsStudents=New ADODB.Recordset rsStudents.Open 研究生研究生,cnGraduate,adCmdTable 前面声明的前面声明的Connection对象对象 表名称表名称 打开的是表对象打开的是表对象 参数占位符参数占位符 7.2.3 7.2.3 关闭关闭Recordset和和Connection对象对象 n方法：执行方法：执行Recordset 对象和对象和Connection对象对象的的Close方法方法 将对象设置为将对象设置为Nothing例如：例如：rsStudents.ClosecnGraduate.CloseSet rsStudents=NothingSet cnGraduate=Nothingn上述语句不是必须的。应用程序终止运行时，系上述语句不是必须的。应用程序终止运行时，系统会自动关闭并清除这两个对象。统会自动关闭并清除这两个对象。7.3 7.3 引用记录字段引用记录字段 n任何对记录集的访问都是针对当前记录进行的。任何对记录集的访问都是针对当前记录进行的。打开记录集时默认的当前记录为第打开记录集时默认的当前记录为第1条记录。条记录。n引用记录的字段：引用记录的字段：直接在记录集对象中直接在记录集对象中引用字段名称引用字段名称，如，如 Code=rsStudents!学号学号若记录集字段名包含空格，或者字段名是一个保留字，若记录集字段名包含空格，或者字段名是一个保留字，则引用时必须将该字段名用方括号括起来。则引用时必须将该字段名用方括号括起来。使用记录集对象的使用记录集对象的Fields(n)属性。属性。n是记录中字段从是记录中字段从左到右的排列序号，第一个字段的序号为左到右的排列序号，第一个字段的序号为0。如。如 Code=rsStudents.Fields(0)记录集对象与字段名间的连接符记录集对象与字段名间的连接符 7.3 7.3 引用记录字段引用记录字段 例例7-1 建立名为建立名为ADO的模块，编写如下过程。运的模块，编写如下过程。运行该过程后，输出对话框显示行该过程后，输出对话框显示“导师导师”表中第一位表中第一位教师的编号和姓名。教师的编号和姓名。7.3 7.3 引用记录字段引用记录字段 n记录集更多的应用是在窗体对象上：建立一个空记录集更多的应用是在窗体对象上：建立一个空白窗体白窗体设计各个控件设计各个控件编程引用记录集当前记编程引用记录集当前记录的相关字段或将字段的值通过控件显示录的相关字段或将字段的值通过控件显示n注意：如果涉及数据访问的事件过程不止一个，注意：如果涉及数据访问的事件过程不止一个，可在代码窗口的通用段定义可在代码窗口的通用段定义Connection对象和对象和Recordset对象，然后在对象，然后在Form_Load事件过程事件过程中完成数据库连接和数据表的打开。中完成数据库连接和数据表的打开。7.3 7.3 引用记录字段引用记录字段 例例7-2 运行：分别单击运行：分别单击“导师编导师编号号”和和“导师姓名导师姓名”按钮按钮 窗体设计视图窗体设计视图(删除导航按钮删除导航按钮)7.3 7.3 引用记录字段引用记录字段 例例7-2窗体程序代码窗体程序代码 7.4 7.4 浏览记录浏览记录 nRecordset记录集对象提供了记录集对象提供了4种记录指针的移动方法种记录指针的移动方法MoveFirst 记录指针移到第一条记录记录指针移到第一条记录MoveNext 记录指针移到当前记录的下一条记录记录指针移到当前记录的下一条记录MovePrevious 记录指针移到当前记录的上一条记录记录指针移到当前记录的上一条记录MoveLast 记录指针移到最后一条记录记录指针移到最后一条记录nRecordset记录集的记录集的BOF和和EOF属性用于判断记录指针属性用于判断记录指针是否处于有记录的正常位置是否处于有记录的正常位置记录指针将指向最后一条记录之后，记录指针将指向最后一条记录之后，EOF属性为属性为True记录指针将指向第一条记录之前，记录指针将指向第一条记录之前，BOF属性为属性为TrueBOF和和EOF属性的值均为属性的值均为True，表示记录集为空，表示记录集为空 7.4 7.4 浏览记录浏览记录 例例7-3 对例对例7-2进行修改进行修改 增加如下事件过程代码：增加如下事件过程代码：Private Sub Command3_Click()单击单击“下一个记录下一个记录”按钮按钮 rsTeacher.MoveNext End Sub7.4 7.4 浏览记录浏览记录 n上面的过程隐含错误：移至最后一条记录后无法上面的过程隐含错误：移至最后一条记录后无法再下移指针，将出现运行错误信息框。再下移指针，将出现运行错误信息框。n两种纠正错误的方法：两种纠正错误的方法：如果记录集的如果记录集的EOF属性为属性为True，就回到第一条记录，就回到第一条记录如果记录集的如果记录集的EOF属性为属性为True，就回到最后一条记录，就回到最后一条记录 Private Sub Command3_Click()单击单击“下一个记录下一个记录”按钮按钮 rsTeacher.MoveNextEnd Sub7.4 7.4 浏览记录浏览记录 Private Sub Command3_Click()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 Sub7.4 7.4 浏览记录浏览记录 n方方法法2的的程程序序还还将将运运行行出出错错。因因为为Recordset对对象象有有一一个个名名为为LockType的的属属性性，默默认认值值为为adLockReadOnly，此此时时只只能能浏浏览览记记录录数数据据，记记录录的的移移动动方方式式也也只只能能是是MoveNext和和MoveFirst。n要实现记录指针的要实现记录指针的4种移动操作，完成记录的添加、删改种移动操作，完成记录的添加、删改或更新，必须在使用或更新，必须在使用Open方法打开数据表之前，将该记方法打开数据表之前，将该记录集对象的录集对象的LockType属性设置成属性设置成adLockPessimistic或或adLockOptimistic，如，如 rsTeacher.LockType=adLockPessimistic rsTeacher.Open 导师导师,cnGraduate,adCmdTable7.4 7.4 浏览记录浏览记录 nRecordset对对象象的的LocyType属属性性决决定定数数据据的的锁锁定方式：定方式：adLockReadOnly：数据处于只读状态。：数据处于只读状态。adLockPessimistic(保保守守式式锁锁定定)：编编辑辑数数据据时时即即锁锁定数据源记录，直到数据编辑完成才释放。定数据源记录，直到数据编辑完成才释放。adLockOptimistic(开开放放式式锁锁定定)：编编辑辑数数据据时时不不锁锁定定，用用Update方法提交数据时才锁定数据源记录。方法提交数据时才锁定数据源记录。adLockBatchOptimistic(开开放放式式更更新新)：应应用用于于批批更更新模式新模式 7.4 7.4 浏览记录浏览记录 n如如果果数数据据表表中中没没有有记记录录，即即BOF和和EOF同同时时为为True，就就不能引用表中的数据。不能引用表中的数据。将单击将单击“导师姓名导师姓名”按钮的事件过程改写如下按钮的事件过程改写如下Private Sub Command2_Click()If rsTeacher.BOF=True And rsTeacher.EOF=True Then Text1.Value=Else Text1.Value=rsTeacher!姓名姓名 End IfEnd Sub同理，修改单击同理，修改单击“导师编号导师编号”按钮的事件过程按钮的事件过程同理，修改同理，修改“下一个记录下一个记录”按钮的事件过程代码按钮的事件过程代码(空表情况下空表情况下记录指针不移动记录指针不移动)7.5 7.5 编辑数据编辑数据 7.5.1 用用ADO记录集的记录集的AddNew方法添加记录方法添加记录7.5.2 用用ADO记录集的记录集的Update方法修改记录方法修改记录 7.5.3 用用ADO记录集的记录集的Delete方法删除记录方法删除记录 7.5.1 7.5.1 7.5.1 7.5.1 用用用用ADOADO记录集的记录集的记录集的记录集的AddNewAddNew方法添加记录方法添加记录方法添加记录方法添加记录添加新记录的步骤：添加新记录的步骤：（1）用记录集的）用记录集的AddNew方法产生一个空记录方法产生一个空记录（2）为空记录的各个字段赋值）为空记录的各个字段赋值（3）用记录集的）用记录集的Update方法保存新记录方法保存新记录例例7-4 在例在例7-3的基础上进行修改的基础上进行修改 7.5.1 7.5.1 7.5.1 7.5.1 用用用用ADOADO记录集的记录集的记录集的记录集的AddNewAddNew方法添加记录方法添加记录方法添加记录方法添加记录添加的程序代码添加的程序代码:Private Sub Command4_Click()Dim Age As Byte rsTeacher.MoveFirst Age=rsTeacher!年龄年龄 读取第一条记录的年龄字段值读取第一条记录的年龄字段值 rsTeacher.AddNew 添加一条新记录添加一条新记录 rsTeacher!导师编号导师编号=107 rsTeacher!姓名姓名=高原高原 rsTeacher!年龄年龄=Age rsTeacher.UpdateEnd Sub思考：什么情况下单击思考：什么情况下单击“新记录新记录”按钮，系统会提示出错按钮，系统会提示出错？7.5.2 7.5.2 7.5.2 7.5.2 用用用用ADOADO记录集的记录集的记录集的记录集的UpdateUpdate方法修改记录方法修改记录方法修改记录方法修改记录n修改记录的步骤：修改记录的步骤：（1）将记录指针移动到需要修改的记录上）将记录指针移动到需要修改的记录上（2）对记录中的相关字段的值进行修改）对记录中的相关字段的值进行修改（3）用）用Update方法保存更改方法保存更改n修改后的结果不得违反数据完整性约束修改后的结果不得违反数据完整性约束 例例7-5 在模块在模块ADO中添加过程中添加过程Update_Age：将：将“导师导师”表中第表中第5条记录的年龄字段值修改为条记录的年龄字段值修改为60岁。岁。注意设置记录集的注意设置记录集的LockType类型类型7.5.2 7.5.2 7.5.2 7.5.2 用用用用ADOADO记录集的记录集的记录集的记录集的UpdateUpdate方法修改记录方法修改记录方法修改记录方法修改记录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 导师导师,cnGraduate,adCmdTable 向下跳过向下跳过4条记录，将记录指针指向第条记录，将记录指针指向第5条记录条记录 For I=1 To 4 rsTeacher.MoveNext Next I rsTeacher!年龄年龄=60:rsTeacher.UpdateEnd Sub7.5.2 7.5.2 7.5.2 7.5.2 用用用用ADOADO记录集的记录集的记录集的记录集的UpdateUpdate方法修改记录方法修改记录方法修改记录方法修改记录 例例7-6在例在例7-4的基础上进行修改的基础上进行修改添加的程序代码添加的程序代码:Private Sub Command5_Click()rsTeacher.MoveFirst Do While Not rsTeacher.EOF If rsTeacher!性别性别=男男 Then rsTeacher!年龄年龄=rsTeacher!年龄年龄+1 rsTeacher.Update End If rsTeacher.MoveNext LoopEnd Sub7.5.3 7.5.3 7.5.3 7.5.3 用用用用ADOADO记录集的记录集的记录集的记录集的DeleteDelete方法删除记录方法删除记录方法删除记录方法删除记录n删除记录要慎重，因为被删记录无法恢复删除记录要慎重，因为被删记录无法恢复 n删除记录的步骤删除记录的步骤:（1）移动到需要删除的记录上）移动到需要删除的记录上（2）用记录集对象的）用记录集对象的Delete方法删除当前记录方法删除当前记录（3）将某条记录指定为当前记录）将某条记录指定为当前记录n删除记录后，删除记录后，Access不能自动使下一条记录成不能自动使下一条记录成为当前记录。根据不同情况，可用为当前记录。根据不同情况，可用MoveNext或或MoveLast方法定位记录指针。方法定位记录指针。7.5.3 7.5.3 7.5.3 7.5.3 用用用用ADOADO记录集的记录集的记录集的记录集的DeleteDelete方法删除记录方法删除记录方法删除记录方法删除记录 例例7-7 对例对例7-6添加如下的单击窗体事件过程：添加如下的单击窗体事件过程：Private Sub Form_Click()rsTeacher.MoveFirst Do While Not rsTeacher.EOF rsTeacher.MoveNext Loop MsgBox 待删除记录不存在！待删除记录不存在！End Sub如果找到如果找到“高原高原”,进行相关操作，然后退出过程进行相关操作，然后退出过程转转下下页页7.5.3 7.5.3 7.5.3 7.5.3 用用用用ADOADO记录集的记录集的记录集的记录集的DeleteDelete方法删除记录方法删除记录方法删除记录方法删除记录 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 If如果找到如果找到“高原高原”，进行相关操作，然后退出过程，进行相关操作，然后退出过程7.67.6 用用ADO技术实现复杂查询技术实现复杂查询 例例7-8 在在ADO模块中建立模块中建立Sex过程：统计并在输过程：统计并在输出对话框中显示男、女研究生人数比。要求以人出对话框中显示男、女研究生人数比。要求以人数多的一方为数多的一方为1、放在右侧、保留小数两位。、放在右侧、保留小数两位。程程序序分分析析：先先遍遍历历全全部部记记录录，分分别别统统计计出出男男、女女研研究究生生人人数数。如如果果男男生生人人数数多多于于女女生生，则则男男生生人人数数为为1，女女生生人人数数改改为为女女生生人人数数除除以以男男生生人人数；反之亦然。数；反之亦然。7.67.6 用用ADO技术实现复杂查询技术实现复杂查询 Sub Sex()（定义对象、完成与数据库的连接、打开研究生表、定义变量）（定义对象、完成与数据库的连接、打开研究生表、定义变量）Student.MoveFirst Do While Not Student.EOF 统计男、女研究生人数统计男、女研究生人数 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 Sub7.67.6 用用ADO技术实现复杂查询技术实现复杂查询 例例7-9 略略例例7-107.67.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.Value=入学日期入学日期 MSFlexGrid1.Col=5:MSFlexGrid1.Value=入学分数入学分数 MSFlexGrid1.Col=6:MSFlexGrid1.Value=研究方向研究方向 MSFlexGrid1.Col=7:MSFlexGrid1.Value=导师编号导师编号End Sub7.67.6 用用ADO技术实现复杂查询技术实现复杂查询 Private Sub Command1_Click()单击单击生成生成按钮按钮（定义变量、定义对象、完成与数据库的连接、打开研究生表）（定义变量、定义对象、完成与数据库的连接、打开研究生表）Student.MoveFirst Do While Not Student.EOF For I=2 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 Sub7.77.7 在在VBA程序中使用程序中使用SQL命令命令 nAccess提供了提供了DoCmd对象，该对象的对象，该对象的RunSQL方法可以在方法可以在VBA程序中用程序中用SQL命令直接对数据源命令直接对数据源进行操作。进行操作。nRunSQL方法的格式为：方法的格式为：DoCmd.RunSQL n也可以也可以Dim As String=DoCmd.RunSQL n是一对由双引号括起来的是一对由双引号括起来的SQL命令命令7.77.7 在在VBA程序中使用程序中使用SQL命令命令 7.7.1 定义数据定义数据7.7.2 编辑编辑数据数据7.7.3 实现数据完整性约束实现数据完整性约束 7.7.4 执行查询操作执行查询操作 7.7.1 7.7.1 定义数据定义数据1.创建数据表创建数据表 CREATE TABLE (字段名字段名 数据类型数据类型 NULL|NOT NULL|字段名字段名 AS 计算表达式计算表达式,.n)例例7-11 在在SQL模块中建立如下过程：模块中建立如下过程：Sub Create_Table()DoCmd.RunSQL 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等等价价7.7.1 7.7.1 定义数据定义数据2.在表中增加字段在表中增加字段 例例7-12 为为Student表表增加增加一个货币型的字段一个货币型的字段“学费学费”3.改变字段的类型改变字段的类型 例例7-13 修改修改Student表中表中“年龄年龄”字段的类型字段的类型Sub Add_Field()DoCmd.RunSQL ALTER TABLE Student ADD 学费学费 CURRENCYEnd SubSub Alter_Fields_Type()DoCmd.RunSQL ALTER TABLE Student ALTER 年龄年龄 SmallIntEnd Sub如果字段的新类型与原类型不兼容将造成数据丢失如果字段的新类型与原类型不兼容将造成数据丢失 7.7.1 7.7.1 定义数据定义数据4.改变字段的宽度改变字段的宽度 例例7-14 更改更改Student表中表中“姓名姓名”字段的宽度字段的宽度 5.删除一个字段删除一个字段 例例7-15 删除删除Student表中的表中的“年龄年龄”字段字段 Sub Delete_Field()DoCmd.RunSQL ALTER TABLE Student DROP 年龄年龄End SubSub Alter_Fields_Width()DoCmd.RunSQL ALTER TABLE Student ALTER 姓名姓名 text(10)End Sub字段的宽度由大变小，有可能丢失数据字段的宽度由大变小，有可能丢失数据 7.7.1 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 StudentEnd Sub7.7.2 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命令命令中。对于字符串变量或日期型变量，要在这些变量的两侧中。对于字符串变量或日期型变量，要在这些变量的两侧加上一对单引号。加上一对单引号。7.7.2 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注意单引号和双引号出现的位置注意单引号和双引号出现的位置 7.7.2 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 Sub7.7.2 7.7.2 编辑编辑数据数据3.删除特定记录删除特定记录 例例7-22 将将“导师导师”表中年龄在表中年龄在50岁以下的记录全部删除岁以下的记录全部删除 Sub Delete_Record()DoCmd.RunSQL DELETE FROM 导师导师 WHERE 年龄年龄50End Sub思考：思考：（1）如果要求将）如果要求将“导师导师”表中低于年龄平均值的导师记表中低于年龄平均值的导师记录删除，例录删除，例7-22应怎样修改？应怎样修改？（2）如果要删除小于）如果要删除小于X岁的所有记录，岁的所有记录，X的值通过键盘的值通过键盘在程序运行时输入，程序又该怎样修改？在程序运行时输入，程序又该怎样修改？7.7.3 7.7.3 实现数据完整性约束实现数据完整性约束 1.设置主键设置主键例例7-23 为为“导师导师”表和表和“研究生研究生”表设置主键表设置主键（如果这两个表已有主键和外键，请先予以删除）（如果这两个表已有主键和外键，请先予以删除）例例7-24 在创建新表在创建新表Teacher 的同时设定的同时设定code为主键为主键 Sub Create_Primary()DoCmd.RunSQL Alter 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 Sub7.7.3 7.7.3 实现数据完整性约束实现数据完整性约束 2.设置外键设置外键例例7-25 将研究生表中的将研究生表中的“导师编号导师编号”设为外键，对应的参设为外键，对应的参照表是照表是“导师导师”表表(建立建立“导师导师”表和表和“研究生研究生”表间的关系表间的关系)例例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)References Teacher(code)End Subbit表示表示“是是/否否”型变量型变量 7.7.4 7.7.4 执行查询操作执行查询操作 VBA程序中用程序中用SQL命令完成的数据查询操作，命令完成的数据查询操作，无法直接将查询结果所返回的记录集按数据表形无法直接将查询结果所返回的记录集按数据表形式显示，解决方法：式显示，解决方法：方方法法1：将将查查询询形形成成的的记记录录集集生生成成一一个个新新表表保保存存到到数数据据库库中中；然然后后用用ADO记记录录集集对对象象打打开开这这个个表表进进行行各各种种操作；完成后删除这个表。操作；完成后删除这个表。方方法法2：将将返返回回的的记记录录集集看看成成是是保保存存在在内内存存中中的的一一个个临临时时表表，用用ADO记记录录集集对对象象直直接接打打开开该该临临时时表表进进行行各各种操作。种操作。7.7.4 7.7.4 执行查询操作执行查询操作例例7-27 Sub Query1()DoCmd.RunSQL Select 姓名姓名,职称职称,年龄年龄 Into 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 职称职称=副教授副教授 7.7.4 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命令实现上题的功能。略命令实现上题的功能。略7.8 7.8 访问当前数据库以外的数据库访问当前数据库以外的数据库 n连接另一个数据库：使用连接另一个数据库：使用Connection对象的对象的Open方法。语法格式为：方法。语法格式为：.Open Provider=;Data Source=;User ID=用户标识用户标识;Password=密码密码;其中：其中：提供者提供者为为Microsoft.Jet.OLEDB.4.0数据库名数据库名包括数据库所在的路径及包括数据库所在的路径及.mdb文件的名字文件的名字用户标识用户标识是用户的名字，缺省为是用户的名字，缺省为admin密码密码省略则表示没有密码省略则表示没有密码7.8 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=;Password=;转转下下页页7.8 7.8 访问当前数据库以外的数据库访问当前数据库以外的数据库 访问访问Departmrnt表表 Dim Unit As ADODB.Recordset Set Unit=New ADODB.Recordset Unit.LockType=adLockOptimistic Unit.Open Department,Dept,adCmdTable 输入导师姓名输入导师姓名 Dim Teacher As String Teacher=InputBox(输入导师姓名输入导师姓名,导师名字导师名字)遍历当前表遍历当前表(导师表导师表)，寻找该导师，寻找该导师 rsTeacher.MoveFirst Do While Not rsTeacher.EOF If rsTeacher!姓名姓名=Teacher Then Exit Do End If rsTeacher.MoveNext Loop转转下下页页接接上上页页找到，则提前退出循环找到，则提前退出循环7.8 7.8 访问当前数据库以外的数据库访问当前数据库以外的数据库 正常退出上面的循环，表示没找到该导师正常退出上面的循环，表示没找到该导师 If rsTeacher.EOF Then MsgBox 未找到未找到&Teacher&！Exit Sub End If Unit.MoveFirst Do While Not Unit.EOF If Unit!系编号系编号=rsTeacher!系编号系编号 Then MsgBox rsTeacher!姓名姓名&Unit!系名系名 Exit Sub End If Unit.MoveNext LoopEnd Sub接接上上页页没找到，则提前退出过程没找到，则提前退出过程7.9 7.9 7.9 7.9 综合实例综合实例综合实例综合实例编制编制编制编制“研究生成绩管理与统计研究生成绩管理与统计研究生成绩管理与统计研究生成绩管理与统计”程序程序程序程序 结束结束