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

.,第7章VBA数据库编程,.,本章内容,记录集概述在Access中引用ADO对象引用记录字段浏览记录编辑数据用ADO技术实现复杂查询在VBA程序中使用SQL命令访问当前数据库以外的数据库综合实例编制“研究生成绩管理与统计”程序,.,7.1记录集概述,7.1.1ADO的9个对象7.1.2了解记录集,.,7.1.1ADO的9个对象,ADO：ActiveXDataObjectsAccess内嵌的VBA是用ADO技术开发数据库应用的主要工具ADO对象模型有9个对象：Connection、Recordset、Record、Command、Parameter、Field、Property、Stream、Error常用对象：Connection、Command、Recordset,.,7.1.1ADO的9个对象,Connection对象：ADO对象模型中最高级的对象，实现应用程序与数据源的连接。Command对象：主要作用是在VBA中通过SQL语句访问、查询数据库中的数据。Recordset对象：存储访问表和查询对象返回的记录。使用该对象，可以浏览记录、修改记录、添加新的记录或者删除特定的记录。Recordset对象的功能最常用、最重要,.,7.1.1ADO的9个对象,ADO的3个对象之间互有联系：Command对象和Recordset对象依赖于Connection对象的连接；Command对象结合SQL命令可以取代Recordset对象，但远没有Recordset对象灵活、实用；Recordset对象它只能实现数据表内记录集操作，无法完成表和数据库的数据定义操作；数据定义操作一般需通过Command对象用SQL命令完成。本章用DoCmd对象代替Command对象。,.,7.1.2了解记录集,记录集(Recordset)：对表执行查询操作时，返回的一组特定记录。用记录集可执行的操作：对表中的数据进行查询和统计，在表中添加、更新或删除记录。记录集是一个对象，它包括记录和字段，具有其特定的属性和方法，利用这些属性和方法就可以编程处理数据库中的记录。,.,7.2在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对象与哪个数据库相连接）例如：DimcnGraduateAsADODB.ConnectionSetcnGraduate=CurrentProject.Connection,.,7.2.2声明与打开Recordset对象,(1)声明Recordset对象(2)创建Recordset对象实例(3)打开Recordset对象例如：DimrsStudentsAsADODB.RecordsetSetrsStudents=NewADODB.RecordsetrsStudents.Open研究生,cnGraduate,adCmdTable,.,7.2.3关闭Recordset和Connection对象,方法：执行Recordset对象和Connection对象的Close方法将对象设置为Nothing例如：rsStudents.ClosecnGraduate.CloseSetrsStudents=NothingSetcnGraduate=Nothing上述语句不是必须的。应用程序终止运行时，系统会自动关闭并清除这两个对象。,.,7.3引用记录字段,任何对记录集的访问都是针对当前记录进行的。打开记录集时默认的当前记录为第1条记录。引用记录的字段：直接在记录集对象中引用字段名称，如Code=rsStudents!学号若记录集字段名包含空格，或者字段名是一个保留字，则引用时必须将该字段名用方括号括起来。使用记录集对象的Fields(n)属性。n是记录中字段从左到右的排列序号，第一个字段的序号为0。如Code=rsStudents.Fields(0),记录集对象与字段名间的连接符,.,7.3引用记录字段,例7-1建立名为ADO的模块，编写如下过程。运行该过程后，输出对话框显示“导师”表中第一位教师的编号和姓名。,.,7.3引用记录字段,记录集更多的应用是在窗体对象上：建立一个空白窗体设计各个控件编程引用记录集当前记录的相关字段或将字段的值通过控件显示注意：如果涉及数据访问的事件过程不止一个，可在代码窗口的通用段定义Connection对象和Recordset对象，然后在Form_Load事件过程中完成数据库连接和数据表的打开。,.,7.3引用记录字段,例7-2,运行：分别单击“导师编号”和“导师姓名”按钮,窗体设计视图(删除导航按钮),.,7.3引用记录字段,例7-2窗体程序代码,.,7.4浏览记录,Recordset记录集对象提供了4种记录指针的移动方法MoveFirst记录指针移到第一条记录MoveNext记录指针移到当前记录的下一条记录MovePrevious记录指针移到当前记录的上一条记录MoveLast记录指针移到最后一条记录Recordset记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置记录指针将指向最后一条记录之后，EOF属性为True记录指针将指向第一条记录之前，BOF属性为TrueBOF和EOF属性的值均为True，表示记录集为空,.,7.4浏览记录,例7-3对例7-2进行修改,增加如下事件过程代码：PrivateSubCommand3_Click()单击“下一个记录”按钮rsTeacher.MoveNextEndSub,.,7.4浏览记录,上面的过程隐含错误：移至最后一条记录后无法再下移指针，将出现运行错误信息框。两种纠正错误的方法：如果记录集的EOF属性为True，就回到第一条记录如果记录集的EOF属性为True，就回到最后一条记录,PrivateSubCommand3_Click()单击“下一个记录”按钮rsTeacher.MoveNextEndSub,.,7.4浏览记录,.,7.4浏览记录,方法2的程序还将运行出错。因为Recordset对象有一个名为LockType的属性，默认值为adLockReadOnly，此时只能浏览记录数据，记录的移动方式也只能是MoveNext和MoveFirst。要实现记录指针的4种移动操作，完成记录的添加、删改或更新，必须在使用Open方法打开数据表之前，将该记录集对象的LockType属性设置成adLockPessimistic或adLockOptimistic，如rsTeacher.LockType=adLockPessimisticrsTeacher.Open导师,cnGraduate,adCmdTable,.,7.4浏览记录,Recordset对象的LocyType属性决定数据的锁定方式：adLockReadOnly：数据处于只读状态。adLockPessimistic(保守式锁定)：编辑数据时即锁定数据源记录，直到数据编辑完成才释放。adLockOptimistic(开放式锁定)：编辑数据时不锁定，用Update方法提交数据时才锁定数据源记录。adLockBatchOptimistic(开放式更新)：应用于批更新模式,.,7.4浏览记录,如果数据表中没有记录，即BOF和EOF同时为True，就不能引用表中的数据。将单击“导师姓名”按钮的事件过程改写如下PrivateSubCommand2_Click()IfrsTeacher.BOF=TrueAndrsTeacher.EOF=TrueThenText1.Value=ElseText1.Value=rsTeacher!姓名EndIfEndSub同理，修改单击“导师编号”按钮的事件过程同理，修改“下一个记录”按钮的事件过程代码(空表情况下记录指针不移动),.,7.5编辑数据,7.5.1用ADO记录集的AddNew方法添加记录7.5.2用ADO记录集的Update方法修改记录7.5.3用ADO记录集的Delete方法删除记录,.,7.5.1用ADO记录集的AddNew方法添加记录,添加新记录的步骤：（1）用记录集的AddNew方法产生一个空记录（2）为空记录的各个字段赋值（3）用记录集的Update方法保存新记录例7-4在例7-3的基础上进行修改,.,7.5.1用ADO记录集的AddNew方法添加记录,添加的程序代码:,PrivateSubCommand4_Click()DimAgeAsBytersTeacher.MoveFirstAge=rsTeacher!年龄读取第一条记录的年龄字段值rsTeacher.AddNew添加一条新记录rsTeacher!导师编号=107rsTeacher!姓名=高原rsTeacher!年龄=AgersTeacher.UpdateEndSub,思考：什么情况下单击“新记录”按钮，系统会提示出错？,.,7.5.2用ADO记录集的Update方法修改记录,修改记录的步骤：（1）将记录指针移动到需要修改的记录上（2）对记录中的相关字段的值进行修改（3）用Update方法保存更改修改后的结果不得违反数据完整性约束例7-5在模块ADO中添加过程Update_Age：将“导师”表中第5条记录的年龄字段值修改为60岁。,注意设置记录集的LockType类型,.,7.5.2用ADO记录集的Update方法修改记录,SubUpdate_Age()DimIAsByteDimcnGraduateAsADODB.ConnectionSetcnGraduate=CurrentProject.ConnectionDimrsTeacherAsADODB.RecordsetSetrsTeacher=NewADODB.RecordsetrsTeacher.LockType=adLockPessimisticrsTeacher.Open导师,cnGraduate,adCmdTable向下跳过4条记录，将记录指针指向第5条记录ForI=1To4rsTeacher.MoveNextNextIrsTeacher!年龄=60:rsTeacher.UpdateEndSub,.,7.5.2用ADO记录集的Update方法修改记录,例7-6在例7-4的基础上进行修改添加的程序代码:,PrivateSubCommand5_Click()rsTeacher.MoveFirstDoWhileNotrsTeacher.EOFIfrsTeacher!性别=男ThenrsTeacher!年龄=rsTeacher!年龄+1rsTeacher.UpdateEndIfrsTeacher.MoveNextLoopEndSub,.,7.5.3用ADO记录集的Delete方法删除记录,删除记录要慎重，因为被删记录无法恢复删除记录的步骤:（1）移动到需要删除的记录上（2）用记录集对象的Delete方法删除当前记录（3）将某条记录指定为当前记录删除记录后，Access不能自动使下一条记录成为当前记录。根据不同情况，可用MoveNext或MoveLast方法定位记录指针。,.,7.5.3用ADO记录集的Delete方法删除记录,例7-7对例7-6添加如下的单击窗体事件过程：,.,7.5.3用ADO记录集的Delete方法删除记录,IfrsTeacher!姓名=高原ThenFlag=MsgBox(是否要删除高原？,vbYesNo,删除确认)IfFlag=vbYesThen确定删除rsTeacher.DeleteMsgBox记录删除完毕。rsTeacher.MoveNext删除记录后设定新的当前记录IfrsTeacher.EOFThenrsTeacher.MoveLastExitSubElseIfFlag=vbNoThen不删除记录MsgBox放弃删除操作！,删除确认ExitSubEndIfEndIf,如果找到“高原”，进行相关操作，然后退出过程,.,7.6用ADO技术实现复杂查询,例7-8在ADO模块中建立Sex过程：统计并在输出对话框中显示男、女研究生人数比。要求以人数多的一方为1、放在右侧、保留小数两位。,程序分析：先遍历全部记录，分别统计出男、女研究生人数。如果男生人数多于女生，则男生人数为1，女生人数改为女生人数除以男生人数；反之亦然。,.,7.6用ADO技术实现复杂查询,SubSex()（定义对象、完成与数据库的连接、打开研究生表、定义变量）Student.MoveFirstDoWhileNotStudent.EOF统计男、女研究生人数IfStudent!性别=男ThenBoy=Boy+1IfStudent!性别=女ThenGirl=Girl+1Student.MoveNextLoopIfGirl=BoyThen以男生人数为1MsgBox女:男=DataSource=;UserID=用户标识;Password=密码;其中：提供者为Microsoft.Jet.OLEDB.4.0数据库名包括数据库所在的路径及.mdb文件的名字用户标识是用户的名字，缺省为admin密码省略则表示没有密码,.,7.8访问当前数据库以外的数据库,例7-30在SQL模块中编写过程Double_Database（Department表保存在C:Manage.mdb数据库中）,SubDouble_Database()(定义对象、连接数据库、设置记录集LockType属性、打开导师表)建立与另一个数据库Department的连接DimDeptAsADODB.ConnectionSetDept=NewADODB.ConnectionDept.OpenProvider=Microsoft.Jet.O