Access-VBA数据库编程(ADO)学习课件

第7章VBA数据库编程1精选课件本章内容

记录集概述

在Access中引用ADO对象

引用记录字段

浏览记录

编辑数据

用ADO技术实现复杂查询

在VBA程序中使用SQL命令

访问当前数据库以外的数据库

综合实例—编制“研究生成绩管理与统计”程序2精选课件7.1记录集概述

7.1.1ADO的9个对象7.1.2了解记录集

3精选课件7.1.1ADO的9个对象

ADO：ActiveXDataObjectsAccess内嵌的VBA是用ADO技术开发数据库应用的主要工具ADO对象模型有9个对象：Connection、Recordset、Record、Command、Parameter、Field、Property、Stream、Error常用对象：Connection、Command、Recordset4精选课件7.1.1ADO的9个对象

Connection对象：ADO对象模型中最高级的对象，实现应用程序与数据源的连接。Command对象：主要作用是在VBA中通过SQL语句访问、查询数据库中的数据。Recordset对象：存储访问表和查询对象返回的记录。使用该对象，可以浏览记录、修改记录、添加新的记录或者删除特定的记录。Recordset对象的功能最常用、最重要5精选课件7.1.1ADO的9个对象

ADO的3个对象之间互有联系：Command对象和Recordset对象依赖于Connection对象的连接；Command对象结合SQL命令可以取代Recordset对象，但远没有Recordset对象灵活、实用；Recordset对象它只能实现数据表内记录集操作，无法完成表和数据库的数据定义操作；数据定义操作一般需通过Command对象用SQL命令完成。本章用DoCmd对象代替Command对象。6精选课件7.1.2了解记录集

记录集(Recordset)：对表执行查询操作时，返回的一组特定记录。用记录集可执行的操作：对表中的数据进行查询和统计，在表中添加、更新或删除记录。记录集是一个对象，它包括记录和字段，具有其特定的属性和方法，利用这些属性和方法就可以编程处理数据库中的记录。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

AsADODB.Connection

Set

cnGraduate=CurrentProject.Connection

与当前数据库连接

9精选课件7.2.2声明与打开Recordset对象

(1)声明Recordset对象(2)创建Recordset对象实例(3)打开Recordset对象例如：

Dim

rsStudents

AsADODB.Recordset

Set

rsStudents=NewADODB.Recordset

rsStudents.Open"研究生",cnGraduate,,,adCmdTable

前面声明的Connection对象

表名称

打开的是表对象

参数占位符

10精选课件7.2.3关闭Recordset和Connection对象

方法：执行Recordset对象和Connection对象的Close方法→将对象设置为Nothing例如：rsStudents.ClosecnGraduate.CloseSet

rsStudents=NothingSet

cnGraduate=Nothing上述语句不是必须的。应用程序终止运行时，系统会自动关闭并清除这两个对象。11精选课件7.3引用记录字段

任何对记录集的访问都是针对当前记录进行的。打开记录集时默认的当前记录为第1条记录。引用记录的字段：直接在记录集对象中引用字段名称，如Code=rsStudents!学号若记录集字段名包含空格，或者字段名是一个保留字，则引用时必须将该字段名用方括号括起来。

使用记录集对象的Fields(n)属性。n是记录中字段从左到右的排列序号，第一个字段的序号为0。如Code=rsStudents.Fields(0)记录集对象与字段名间的连接符

12精选课件7.3引用记录字段

[例7-1]建立名为ADO的模块，编写如下过程。运行该过程后，输出对话框显示“导师”表中第一位教师的编号和姓名。13精选课件7.3引用记录字段

记录集更多的应用是在窗体对象上：建立一个空白窗体→设计各个控件→编程引用记录集当前记录的相关字段或将字段的值通过控件显示注意：如果涉及数据访问的事件过程不止一个，可在代码窗口的通用段定义Connection对象和Recordset对象，然后在Form_Load事件过程中完成数据库连接和数据表的打开。14精选课件7.3引用记录字段

[例7-2]运行：分别单击“导师编号”和“导师姓名”按钮

窗体设计视图(删除导航按钮)

15精选课件7.3引用记录字段

例7-2窗体程序代码

16精选课件7.4浏览记录

Recordset记录集对象提供了4种记录指针的移动方法MoveFirst记录指针移到第一条记录MoveNext记录指针移到当前记录的下一条记录MovePrevious记录指针移到当前记录的上一条记录MoveLast记录指针移到最后一条记录Recordset记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置记录指针将指向最后一条记录之后，EOF属性为True记录指针将指向第一条记录之前，BOF属性为TrueBOF和EOF属性的值均为True，表示记录集为空

17精选课件7.4浏览记录

[例7-3]对例7-2进行修改

增加如下事件过程代码：

PrivateSubCommand3_Click()‘单击“下一个记录”按钮rsTeacher.MoveNext

EndSub18精选课件7.4浏览记录

上面的过程隐含错误：移至最后一条记录后无法再下移指针，将出现运行错误信息框。两种纠正错误的方法：如果记录集的EOF属性为True，就回到第一条记录如果记录集的EOF属性为True，就回到最后一条记录

PrivateSubCommand3_Click()‘单击“下一个记录”按钮rsTeacher.MoveNextEndSub19精选课件7.4浏览记录

PrivateSubCommand3_Click()

rsTeacher.MoveNextIfrsTeacher.EOFThenrsTeacher.MoveFirstEndIfEndSub方法1：若记录集的EOF属性为True，则回到首记录方法2：若记录集的EOF属性为True，则回到末记录PrivateSubCommand3_Click()

rsTeacher.MoveNextIfrsTeacher.EOFThenrsTeacher.MoveLastEndIfEndSub20精选课件7.4浏览记录

方法2的程序还将运行出错。因为Recordset对象有一个名为LockType的属性，默认值为adLockReadOnly，此时只能浏览记录数据，记录的移动方式也只能是MoveNext和MoveFirst。要实现记录指针的4种移动操作，完成记录的添加、删改或更新，必须在使用Open方法打开数据表之前，将该记录集对象的LockType属性设置成adLockPessimistic或adLockOptimistic，如rsTeacher.LockType=adLockPessimisticrsTeacher.Open"导师",cnGraduate,,,adCmdTable21精选课件7.4浏览记录

Recordset对象的LocyType属性决定数据的锁定方式：adLockReadOnly：数据处于只读状态。adLockPessimistic(保守式锁定)：编辑数据时即锁定数据源记录，直到数据编辑完成才释放。adLockOptimistic(开放式锁定)：编辑数据时不锁定，用Update方法提交数据时才锁定数据源记录。adLockBatchOptimistic(开放式更新)：应用于批更新模式22精选课件7.4浏览记录

如果数据表中没有记录，即BOF和EOF同时为True，就不能引用表中的数据。将单击“导师姓名”按钮的事件过程改写如下PrivateSubCommand2_Click()IfrsTeacher.BOF=TrueAndrsTeacher.EOF=TrueThenText1.Value=""ElseText1.Value=rsTeacher!姓名EndIfEndSub同理，修改单击“导师编号”按钮的事件过程同理，修改“下一个记录”按钮的事件过程代码(空表情况下记录指针不移动)23精选课件7.5编辑数据

7.5.1用ADO记录集的AddNew方法添加记录7.5.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方法添加记录添加的程序代码:PrivateSubCommand4_Click()DimAgeAs

BytersTeacher.MoveFirstAge=rsTeacher!年龄'读取第一条记录的年龄字段值

rsTeacher.AddNew

'添加一条新记录rsTeacher!导师编号="107"rsTeacher!姓名="高原"rsTeacher!年龄=Age

rsTeacher.UpdateEndSub思考：什么情况下单击“新记录”按钮，系统会提示出错？26精选课件7.5.2用ADO记录集的Update方法修改记录修改记录的步骤：（1）将记录指针移动到需要修改的记录上（2）对记录中的相关字段的值进行修改（3）用Update方法保存更改修改后的结果不得违反数据完整性约束

[例7-5]在模块ADO中添加过程Update_Age：将“导师”表中第5条记录的年龄字段值修改为60岁。注意设置记录集的LockType类型27精选课件7.5.2用ADO记录集的Update方法修改记录SubUpdate_Age()

DimIAsByte

DimcnGraduateAsADODB.Connection

SetcnGraduate=CurrentProject.Connection

DimrsTeacherAsADODB.Recordset

SetrsTeacher=NewADODB.Recordset

rsTeacher.LockType=adLockPessimisticrsTeacher.Open"导师",cnGraduate,,,adCmdTable

'向下跳过4条记录，将记录指针指向第5条记录ForI=1To4rsTeacher.MoveNextNextIrsTeacher!年龄=60:rsTeacher.UpdateEndSub28精选课件7.5.2用ADO记录集的Update方法修改记录[例7-6]在例7-4的基础上进行修改添加的程序代码:

PrivateSubCommand5_Click()rsTeacher.MoveFirst

DoWhileNotrsTeacher.EOF

IfrsTeacher!性别="男"ThenrsTeacher!年龄=rsTeacher!年龄+1rsTeacher.UpdateEndIfrsTeacher.MoveNextLoopEndSub29精选课件7.5.3用ADO记录集的Delete方法删除记录删除记录要慎重，因为被删记录无法恢复删除记录的步骤:（1）移动到需要删除的记录上（2）用记录集对象的Delete方法删除当前记录（3）将某条记录指定为当前记录删除记录后，Access不能自动使下一条记录成为当前记录。根据不同情况，可用MoveNext或MoveLast方法定位记录指针。

30精选课件7.5.3用ADO记录集的Delete方法删除记录[例7-7]对例7-6添加如下的单击窗体事件过程：PrivateSubForm_Click()rsTeacher.MoveFirst

DoWhileNotrsTeacher.EOF

rsTeacher.MoveNext

LoopMsgBox"待删除记录不存在！"EndSub如果找到“高原”,进行相关操作，然后退出过程转下页31精选课件7.5.3用ADO记录集的Delete方法删除记录

IfrsTeacher!姓名="高原"ThenFlag=MsgBox("是否要删除高原？",vbYesNo,"删除确认")IfFlag=vbYesThen

'确定删除rsTeacher.DeleteMsgBox"记录删除完毕。"rsTeacher.MoveNext'删除记录后设定新的当前记录

IfrsTeacher.EOFThenrsTeacher.MoveLast

ExitSubElseIfFlag=vbNoThen

'不删除记录MsgBox"放弃删除操作！",,"删除确认"

ExitSubEndIfEndIf如果找到“高原”，进行相关操作，然后退出过程32精选课件7.6用ADO技术实现复杂查询

[例7-8]在ADO模块中建立Sex过程：统计并在输出对话框中显示男、女研究生人数比。要求以人数多的一方为1、放在右侧、保留小数两位。程序分析：先遍历全部记录，分别统计出男、女研究生人数。如果男生人数多于女生，则男生人数为1，女生人数改为女生人数除以男生人数；反之亦然。

33精选课件7.6用ADO技术实现复杂查询

SubSex()（定义对象、完成与数据库的连接、打开研究生表、定义变量）Student.MoveFirst

DoWhile

NotStudent.EOF'统计男、女研究生人数

IfStudent!性别="男"ThenBoy=Boy+1IfStudent!性别="女"ThenGirl=Girl+1Student.MoveNextLoopIfGirl<=BoyThen

'以男生人数为1MsgBox"女:男="&Format(Girl/Boy,"0.00")&":1"Else

'以女生人数为1MsgBox"男:女="&Format(Boy/Girl,"0.00")&":1"EndIfEndSub34精选课件7.6用ADO技术实现复杂查询

[例7-9]略[例7-10]35精选课件7.6用ADO技术实现复杂查询

PrivateSubForm_Load()MSFlexGrid1.Rows=1MSFlexGrid1.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="导师编号"EndSub36精选课件7.6用ADO技术实现复杂查询

PrivateSubCommand1_Click()'单击"生成"按钮（定义变量、定义对象、完成与数据库的连接、打开研究生表）Student.MoveFirst

DoWhileNotStudent.EOFForI=2ToStudent!入学分数-1

IfStudent!入学分数ModI=0ThenExitFor

'不是质数NextI

IfI=Student!入学分数Then

'如果入学分数为质数S=""

ForI=0To6'将当前记录各个字段连成一行

S=S&Student.Fields(I)&Chr(9)

NextIMSFlexGrid1.AddItem“”&vbTab&S'跳过每行左侧的标题栏

EndIfStudent.MoveNextLoopEndSub37精选课件7.7

在VBA程序中使用SQL命令

Access提供了DoCmd对象，该对象的RunSQL方法可以在VBA程序中用SQL命令直接对数据源进行操作。RunSQL方法的格式为：DoCmd.RunSQL<SQL命令>也可以Dim<字符串变量>AsString<字符串变量>=<SQL命令>DoCmd.RunSQL<字符串变量>

<SQL命令>是一对由双引号括起来的SQL命令38精选课件7.7

在VBA程序中使用SQL命令

7.7.1定义数据7.7.2编辑数据7.7.3实现数据完整性约束

7.7.4执行查询操作

39精选课件7.7.1定义数据1.创建数据表

CREATETABLE<表名>(字段名数据类型[NULL|NOTNULL]|字段名AS计算表达式[,...n])[例7-11]在SQL模块中建立如下过程：

SubCreate_Table()

DoCmd.RunSQL"CREATETABLEStudent(姓名text(6),年龄byte,入学日期date)"EndSubSubCreate_Table()DimSqlAsString

Sql="CREATETABLEStudent(姓名text(6),年龄byte,入学日期date)"DoCmd.RunSQLSqlEndSub等价40精选课件7.7.1定义数据2.在表中增加字段

[例7-12]为Student表增加一个货币型的字段“学费”

3.改变字段的类型[例7-13]修改Student表中“年龄”字段的类型SubAdd_Field()DoCmd.RunSQL"ALTERTABLEStudentADD学费CURRENCY"EndSubSubAlter_Fields_Type()DoCmd.RunSQL"ALTERTABLEStudentALTER年龄SmallInt"EndSub如果字段的新类型与原类型不兼容将造成数据丢失

41精选课件7.7.1定义数据4.改变字段的宽度

[例7-14]更改Student表中“姓名”字段的宽度

5.删除一个字段

[例7-15]删除Student表中的“年龄”字段

SubDelete_Field()DoCmd.RunSQL"ALTERTABLEStudentDROP

年龄"EndSubSubAlter_Fields_Width()DoCmd.RunSQL"ALTERTABLEStudentALTER姓名text(10)"EndSub字段的宽度由大变小，有可能丢失数据

42精选课件7.7.1定义数据6.删除一个表

[例7-16]删除Student表

7.修改数据表名字

[例7-17]将Student表的名字更改成“学生”SubRename_Table()

DoCmd.Rename"学生",acTable,"Student"EndSubSubDelete_Table()DoCmd.RunSQL"DROPTABLEStudent"EndSub43精选课件7.7.2编辑数据1.向表中追加记录[例7-18]在Student表中添加一条记录SubInsert_Table()DoCmd.RunSQL"INSERTINTOStudentVALUES('李大明',35,'2003-1-15')"EndSubVALUES后的数据与表中字段的顺序要一一对应

说明：（1）日期型常量可以放在一对单引号或一对#中。（2）如果数据是通过输入对话框等途径获得，需在SQL命令中使用变量，然后用&运算符将变量连接到SQL命令中。对于字符串变量或日期型变量，要在这些变量的两侧加上一对单引号。44精选课件7.7.2编辑数据[例7-19]通过变量为Student表添加一条记录

SubInsert_Table_VBA()

DimS_nameAsString

DimAgeAsByte,S_dateAsDateS_name=InputBox("输入学生姓名：")S_date=InputBox("入学日期：")Age=21DoCmd.RunSQL"INSERTINTOStudentVALUES('"&S_name&"',"&Age&",'"&S_date&"')"EndSub注意单引号和双引号出现的位置

45精选课件7.7.2编辑数据2.修改表中记录[例7-20]将“导师”表中“李向明”的年龄改成40

[例7-21]将“导师”表中所有男导师的年龄增加1岁SubUpdate_Table_1()DoCmd.RunSQL"UPDATE导师SET年龄=40WHERE姓名='李向明'"EndSub如果本例用ADO技术编程，代码会怎样？SubUpdate_Table_2()DoCmd.RunSQL"UPDATE导师SET年龄=年龄+1WHERE性别='男'"EndSub46精选课件7.7.2编辑数据3.删除特定记录

[例7-22]将“导师”表中年龄在50岁以下的记录全部删除

SubDelete_Record()DoCmd.RunSQL"DELETEFROM导师WHERE年龄<50"EndSub思考：（1）如果要求将“导师”表中低于年龄平均值的导师记录删除，例7-22应怎样修改？（2）如果要删除小于X岁的所有记录，X的值通过键盘在程序运行时输入，程序又该怎样修改？47精选课件7.7.3实现数据完整性约束

1.设置主键[例7-23]为“导师”表和“研究生”表设置主键（如果这两个表已有主键和外键，请先予以删除）[例7-24]在创建新表Teacher的同时设定code为主键

SubCreate_Primary()DoCmd.RunSQL"AlterTable导师AddPrimaryKey(导师编号)"DoCmd.RunSQL"AlterTable研究生AddPrimaryKey(学号)"EndSubSubCreate_Table_Primary()DoCmd.RunSQL"CREATETABLETeacher(codetext(3)PRIMARYKEY,nametext(6),birthdaydate,salarycurrency)"EndSub48精选课件7.7.3实现数据完整性约束

2.设置外键[例7-25]将研究生表中的“导师编号”设为外键，对应的参照表是“导师”表(建立“导师”表和“研究生”表间的关系)[例7-26]在创建新表Student1的同时指定其外键和参照表SubCreate_Foreign()DoCmd.RunSQL"AlterTable研究生AddForeignKey(导师编号)References导师)"EndSubSubCreate_Table_Foreign()DoCmd.RunSQL"CreateTableStudent1(codetext(4)PrimaryKey,nametext(6),sexbit,t_codetext(3),ForeignKey(t_code)ReferencesTeacher(code))"EndSubbit表示“是/否”型变量

49精选课件7.7.4执行查询操作VBA程序中用SQL命令完成的数据查询操作，无法直接将查询结果所返回的记录集按数据表形式显示，解决方法：

方法1：将查询形成的记录集生成一个新表保存到数据库中；然后用ADO记录集对象打开这个表进行各种操作；完成后删除这个表。方法2：将返回的记录集看成是保存在内存中的一个临时表，用ADO记录集对象直接打开该临时表进行各种操作。50精选课件7.7.4执行查询操作[例7-27]

SubQuery1()DoCmd.RunSQL"Select姓名,职称,年龄IntotempFrom导师Where职称in('教授','副教授')"(定义对象、连接数据库、设置记录集LockType属性、打开temp表)

DoWhileNotrsTeacher.EOF

Debug.PrintrsTeacher!姓名,rsTeacher!职称,rsTeacher!年龄rsTeacher.MoveNext

LooprsTeacher.Close'关闭记录集对象，终止对temp的引用DoCmd.RunSQL"DropTabletemp"'删除数据表tempEndSub删除temp表之前必须先关闭对表的引用

职称='教授'Or职称='副教授'

51精选课件7.7.4执行查询操作[例7-28]

Sub

Query2()DimSQLAsStringSQL="SELECTt.姓名as导师姓名,s.姓名as学生姓名FROM导师t,研究生sWHEREt.导师编号=s.导师编号andt.性别='男'"(定义对象、连接数据库、设置记录集LockType属性、打开temp表)rsTeacher.OpenSQL,cnGraduate'打开查询记录集DoWhileNotrsTeacher.EOFDebug.Print

rsTeacher!导师姓名,rsTeacher!学生姓名rsTeacher.MoveNextLoopEndSubt、s分别作为“导师”表和“研究生”表的别名

[例7-29]不使用SQL命令实现上题的功能。略52精选课件7.8访问当前数据库以外的数据库连接另一个数据库：使用Connection对象的Open方法。语法格式为：