VBA数据库编程.ppt

第九章第九章 VBA数据库编程数据库编程二级二级ACCESS数据库及表数据库及表数据库及表数据库及表本章的重要性本章的重要性本章的重要性本章的重要性v2007200720072007年年年年4 4 4 4月月月月-2010-2010-2010-2010年年年年9 9 9 9月共月共月共月共8 8 8 8次计算机等级考试次计算机等级考试次计算机等级考试次计算机等级考试中涉及到本章内容的题目有中涉及到本章内容的题目有中涉及到本章内容的题目有中涉及到本章内容的题目有20202020题，平均每次题，平均每次题，平均每次题，平均每次4.54.54.54.5分，因此本章不必花大多时间去准备。分，因此本章不必花大多时间去准备。分，因此本章不必花大多时间去准备。分，因此本章不必花大多时间去准备。v本章在考试中不但涉及笔试题目，一般没有本章在考试中不但涉及笔试题目，一般没有本章在考试中不但涉及笔试题目，一般没有本章在考试中不但涉及笔试题目，一般没有上机题目。上机题目。上机题目。上机题目。二级二级ACCESSACCESS数据访问页数据访问页关键考点关键考点 v数据访问对象数据访问对象DAODAO概念概念 vActiveActive数据对象数据对象ADOADO概念概念 v数据库编程的一些基本语句数据库编程的一些基本语句 v几个函数几个函数二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程（P257P257）VBAVBA通过数据库引擎通过数据库引擎(MicrosofMicrosof jet)jet)工具完成对数工具完成对数据库的访问，所谓数据库引擎其实是一组动态连接库据库的访问，所谓数据库引擎其实是一组动态连接库(DLL),(DLL),程序运行时被连接到程序运行时被连接到VBAVBA程序程序而实现对数据程序程序而实现对数据库的访问功能。库的访问功能。VBAVBA中提供了中提供了3 3种数据库访问接口种数据库访问接口。vODBC APIODBC API：开放数据库互连应用编程接口。开放数据库互连应用编程接口。vDAODAO：数据访问对象。数据访问对象。vADOADO：ActiveActive数据对象数据对象 VBAVBA访问的三种数据库类型：访问的三种数据库类型：v本地数据库：本地数据库：ACCESS ACCESS v外部数据库外部数据库 vODBCODBC数据库：数据库：如如OracleOracle、SyBaseSyBase、SQL ServerSQL Server等等 二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程vDAODAO数据访问对象模型数据访问对象模型（P259P259）DBEngineWorkspace(s)Database(s)RecordSet(s)Field(s)Error(s)QueryDef(s)DBEngineDBEngine数据库引擎为数据库引擎为DAODAO模型最上层对象，模型最上层对象，包含并管理其它对象。包含并管理其它对象。二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程vADOADO ActiveActive数据对象模型模型数据对象模型模型(P260)(P260)Error(s)ConnectionCommandRecordSetField(s)指定数据连接提供者，建指定数据连接提供者，建立到数据源的连接。立到数据源的连接。可执行一个命令，如可执行可执行一个命令，如可执行SQL查询。一般用于表结构的修改。查询。一般用于表结构的修改。可执行可执行SQL查询返回一个记录集，查询返回一个记录集，并可对记录集进行修改、插入、并可对记录集进行修改、插入、删除操作。是删除操作。是ADO中最灵活的对中最灵活的对象。象。二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程v例：分别用例：分别用DAODAO和和ADOADO来完成对来完成对“教学管理教学管理.mdbmdb”文文件中件中“学生表学生表”的学生年龄都加的学生年龄都加1 1的操作。（假设文的操作。（假设文件存放在件存放在E E盘盘“考试中心考试中心”文件夹中）文件夹中）（P270P270）使用使用DAODAO技术：技术：Sub SetAgePlus1()dim we as DAO.Workspace 工作区对象工作区对象 dim db as DAO.Database 数据库对象数据库对象 dim rs as DAO.Recordset 记录集对象记录集对象 dim fd as DAO.Field 字段对象字段对象 set ws=DBEngine.Workspace(0)set db=ws.OpenDatabase(“e:考试中心教程考试中心教程教学管理教学管理.mdb”)打开数据打开数据库库 set rs=db.OpenRecordSet(“学生表学生表”)返回返回“学生表学生表”记录集记录集 set fd=rs.Fields(“年龄年龄”)设置设置“年龄年龄”字段字段二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程 对记录集用循环结构进行遍历对记录集用循环结构进行遍历 Do while not rs.eof rs.edit 设置为设置为“编辑编辑”状态状态 fd=fd+1 “年龄年龄”加加1 rs.update 更新记录，保存年龄值更新记录，保存年龄值 rs.movenext 记录指针移动至下一条记录指针移动至下一条 Loop 关闭并回收对象变量关闭并回收对象变量 rs.close db.close set rs=Nothing set db=Nothing End sub二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程使用使用ADOADO技术：技术：Sub SetAgePlus1()dim cn as New ADODB.Connection 连接对象连接对象 dim rs as New ADODB.RecordSet 记录集对象记录集对象 dim fs as ADODB.Field 字段对象字段对象 dim strConnect as String 连接字符串连接字符串 dim strSQL as String 查询字符串查询字符串 strconnect=“e:考试中心教程考试中心教程教学管理教学管理.mdb”)设置连接数据库设置连接数据库 cn.Provider=“Microsoft.jet.oledb.4.0”设置数据提供者设置数据提供者 cn.open strconnect 打开与数据源的连接打开与数据源的连接 strSQL=“select 年龄年龄 from 学生表学生表”设置查询语句设置查询语句 rs.open strSQL,cn,adOpenDynamic,adLockOptimistic,adCmdText set fd=rs.Fields(“年龄年龄”)二级二级ACCESS模块与模块与模块与模块与VBAVBA10.12 VBA10.12 VBA数据库编程数据库编程 对记录集用循环结构进行遍历对记录集用循环结构进行遍历 Do while not rs.eof fd=fd+1 “年龄年龄”加加1 rs.update 更新记录，保存年龄值更新记录，保存年龄值 rs.movenext 记录指针移动至下一条记录指针移动至下一条 Loop 关闭并回收对象变量关闭并回收对象变量 rs.close db.close set rs=Nothing set db=Nothing End sub二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程vADOADO数据对象模型的相关要点数据对象模型的相关要点（不掌握）（不掌握）声明的声明的Connection对象对象 数据集数据集 打开的对象打开的对象 记录集的打开方式记录集的打开方式 rs.open strSQL,cn,adOpenDynamic,adLockOptimistic,adCmdTextRecordsetRecordset对象的对象的LocyTypeLocyType属性决定数据的锁定方式属性决定数据的锁定方式 adLockReadOnly：数据处于只读状态。：数据处于只读状态。adLockPessimistic(保守式锁定保守式锁定)：编辑数据时即锁定数据源记录，直到：编辑数据时即锁定数据源记录，直到数据编辑完成才释放。数据编辑完成才释放。adLockOptimistic(开放式锁定开放式锁定)：编辑数据时不锁定，用：编辑数据时不锁定，用UpdateUpdate方法提方法提交数据时才锁定数据源记录。交数据时才锁定数据源记录。adLockBatchOptimistic(开放式更新开放式更新)：应用于批更新模式：应用于批更新模式二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程（P265P265）RecordsetRecordset记录集对象记录指针的移动方法记录集对象记录指针的移动方法 MoveFirst 记录指针移到第一条记录记录指针移到第一条记录 MoveNext 记录指针移到当前记录的下一条记录记录指针移到当前记录的下一条记录 MovePrevious 记录指针移到当前记录的上一条记录记录指针移到当前记录的上一条记录 MoveLast 记录指针移到最后一条记录记录指针移到最后一条记录RecordsetRecordset记录集的记录集的BOFBOF和和EOFEOF属性用于判断记录指针属性用于判断记录指针是否处于有记录的正常位置是否处于有记录的正常位置 记录指针将指向最后一条记录之后，记录指针将指向最后一条记录之后，EOFEOF属性为属性为TrueTrue 记录指针将指向第一条记录之前，记录指针将指向第一条记录之前，BOFBOF属性为属性为TrueTrue BOFBOF和和EOFEOF属性的值均为属性的值均为TrueTrue，表示记录集为空表示记录集为空 二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程NzNz函数函数：将将NullNull值转换为值转换为0 0，空字符串，及其他指定值，空字符串，及其他指定值 语法：语法：NzNz(表达式或字段属性值表达式或字段属性值,指定值指定值)指定值省略时，将数值型字段中的指定值省略时，将数值型字段中的NULLNULL转换为转换为0 0，将字符型字，将字符型字段的段的NULLNULL转换为空字符串转换为空字符串”。指定值给定，则将指定值给定，则将NULLNULL转换为指定值转换为指定值v数据库访问的几个重要函数数据库访问的几个重要函数（P276P276）DLookupDLookup函数函数：从指定记录集中搜索特定字段的值。从指定记录集中搜索特定字段的值。语法：语法：DLookUpDLookUp(表达式表达式,记录集记录集,条件式条件式)表达式用户返回检索字段。表达式用户返回检索字段。如果条件返回多个记录，则返回第一个记录相应字段的值如果条件返回多个记录，则返回第一个记录相应字段的值 DLookUpDLookUp(“学生姓名学生姓名”,“学生表学生表”,”学生姓名学生姓名=张三张三”)二级二级ACCESS模块与模块与模块与模块与VBAVBA9.2 VBA9.2 VBA数据库编程数据库编程DCount(表达式表达式,记录集记录集,条件式条件式)：统计记录个数统计记录个数 DAvg(表达式表达式,记录集记录集,条件式条件式)：统计某字段的平均值统计某字段的平均值 DSum(表达式表达式,记录集记录集,条件式条件式)：对某字段求和对某字段求和 表达式表达式字段字段 记录集记录集表名、查询或表名、查询或SQL SQL 条件式条件式条件表达式条件表达式v数据库访问的几个重要函数数据库访问的几个重要函数 例：例：DCount(“编号编号”,“教师表教师表”,”性别性别=男男”)DAvg(“成绩成绩”,“选课成绩选课成绩”)DSum(“订单数订单数”,“订单表订单表”)【2010.3】(1)能够实现从指定记录集里检索特定字段能够实现从指定记录集里检索特定字段值的函数是值的函数是【】A)NzB)FindC)LookupD)DLookup【2010.3】(2)下列程序段的功能是实现下列程序段的功能是实现“学生学生”表中表中“年龄年龄”字段值加字段值加1Dim Str As StringStr=Docmd.RunSQL Str空白处应填入的程序代码是空白处应填入的程序代码是【】A)年龄年龄=年龄年龄+1B)Update学生学生 Set 年龄年龄=年龄年龄+1C)Set 年龄年龄=年龄年龄+1 D)Edit 学生学生 年龄年龄=年龄年龄+l二级二级ACCESSACCESS数据访问页数据访问页过关练习过关练习DB【2010.3】(1)能够实现从指定记录集里检索特定字段能够实现从指定记录集里检索特定字段值的函数是值的函数是【】A)NzB)FindC)LookupD)DLookup【2010.3】(2)下列程序段的功能是实现下列程序段的功能是实现“学生学生”表中表中“年龄年龄”字段值加字段值加1Dim Str As StringStr=Docmd.RunSQL Str空白处应填入的程序代码是空白处应填入的程序代码是【】A)年龄年龄=年龄年龄+1B)Update学生学生 Set 年龄年龄=年龄年龄+1C)Set 年龄年龄=年龄年龄+1 D)Edit 学生学生 年龄年龄=年龄年龄+l二级二级ACCESSACCESS数据访问页数据访问页过关练习过关练习DB【2009.9】(3)下列过程的功能是：通过对象变量返回下列过程的功能是：通过对象变量返回当前窗体的当前窗体的Recordset属性记录集引用，消息框中输出属性记录集引用，消息框中输出记录集的记录（即窗体记录源）个数。记录集的记录（即窗体记录源）个数。【】Sub GetRecNum()Dim rs As ObjectSet rs=Me.RecordsetMsgBox【】End Sub程序空白处应填写的是程序空白处应填写的是A）Count B）rs.CountC）RecordCountD）rs.RecordCount二级二级ACCESSACCESS数据访问页数据访问页过关练习过关练习D【2009.3】(4)能够实现从指定记录集里检索特定字段能够实现从指定记录集里检索特定字段的函数是的函数是【】A）Dcount B）DLookup C）DMax D）DSum【2008.4】(5)ADO 的含义是的含义是【】A）开放数据库互连应用编程接口）开放数据库互连应用编程接口 B）数据库访问对象）数据库访问对象C）动态链接库）动态链接库 D）Active 数据对象数据对象【2007.9】(6)在在Access中，中，DAO的含义是的含义是【】A）开放数据库互连应用编程接口）开放数据库互连应用编程接口 B）数据库访问对象）数据库访问对象C）Active数据对象数据对象 D）数据库动态链接库）数据库动态链接库二级二级ACCESSACCESS数据访问页数据访问页过关练习过关练习BDB二级二级ACCESSACCESS数据访问页数据访问页过关练习过关练习BDBPrivate Sub Commands_Click（）（）Dim db As DAO.DatabaseDim rs As DAO.RecordsetDim zc As DAO.FieldDim Countl As Integer,Count2 As Integer,Count3 As IntegerSet db=CurrentDb（）（）Set rs=db.OpenRecordset（”职工基本情况表职工基本情况表“）Set zc=rs.Fields（”职称职称“）Countl=0:Count2=0:Count3=0Do While Not【】Select Case zcCase Is=”教授教授“Countl=Countl+1CaseIs=”副教授副教授“Count2=Count2+1Case ElseCourit3=Count3+1End Select【】Looprs.CloseSet rs=NothingSet db=NothingMsgBox”教授：教授：“&Count1&”,副教授：副教授：“&Count2&”,其他：其他：“&count3End Sub【2010.9】(1)数据库的数据库的”职职I基本基本情况表情况表“有有”姓名姓名“和和”职称职称“等等字段，要分别统计教授、副教授和字段，要分别统计教授、副教授和其他人员的数量。请在空白处填入其他人员的数量。请在空白处填入适当语句，使程序可以完成指定的适当语句，使程序可以完成指定的功能。功能。rs.EOFrs.MoveNext