Access数据库技术及应用第10章-VBA数据库编程

Access数据库技术及应用第10章-VBA数据库编程第10章VBA数据库编程10.1VBA数据库编程相关基础知识10.2数据访问对象10.3ActiveX数据对象10.4　特殊域聚合函数和RunSQL方法10.5综合案例第1章数据库基础知识210.1VBA数据库编程相关基础知识10.1.1VBA数据库应用程序一般框架10.1.2VBA数据库访问接口10.1.3VBA访问的数据类型第1章数据库基础知识310.1.1VBA数据库应用程序一般框架它由3部分组成:应用程序界面是用户和应用程序的接口，用来输入和展现数据信息；数据库负责数据的存储；数据库引擎实际上是一组动态链接库（DLL），当程序运行时被连接到VBA程序而实现对数据库数据的访问功能第1章数据库基础知识410.1.2VBA数据库访问接口（1）开放数据库互连应用编程接口（OpenDatabaseConnectivityAPI，ODBCAPI）（2）数据访问对象（DataAccessObject，DAO）（3）ActiveX数据对象（ActiveXDataObjects，ADO）第1章数据库基础知识510.1.3VBA访问的数据类型VBA访问的数据库有3种：（1）本地数据库本地数据库文件格式与MicrosoftAccess相同。Jet引擎直接创建和操作这些数据库。（2）外部数据库访问符合“索引顺序访问文件方法（ISAM）”数据库，包括dBaseIII、dBaseIV、Foxpro2.0和2.5以及Paradox3.x和4.x。（3）ODBC数据库访问符合ODBC 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 的客户机/服务器数据库，如MicrosoftSQLServer。第1章数据库基础知识610.2数据访问对象10.2.1DAO库的引用方法10.2.2DAO模型层次结构10.2.3利用DAO访问数据库的一般步骤第1章数据库基础知识710.2.1DAO库的引用方法将“MicrosoftOffice14.0AccessdatabaseengineObjectLibrary”选项选中，在Windows7环境下，默认该项引用是选中的。第1章数据库基础知识810.2.2DAO模型层次结构第1章数据库基础知识9①DBEngine对象： 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 示数据库引擎，包含并控制模型中的其他对象。②WorkSpace对象：表示工作区。③DataBase对象：表示操作的数据库对象。④RecordSet对象：表示数据操作返回的记录集，可以来自于表、查询或SQL语句的运行结果。⑤Field对象：代表在数据集中的某一列。⑥QueryDef对象：表示数据库查询信息。⑦Error对象：包含使用DAO对象产生的错误信息。10.2.3利用DAO访问数据库的一般步骤（1）首先定义各个对象变量DimwsAsDAO.Workspace'定义Workspace对象DimdbAsDAO.Database'定义数据库对象DimrsAsDAO.Recordset'定义记录集对象（2）为各个对象赋值Setws=DBEngine.Workspaces(0)'将默认工作空间赋值给wsSetdb=ws.OpenDatabase("需要打开的数据库")'打开指定的数据库Setrs=db.OpenRecordset(<表、查询、SQL语句>)'打开指定记录集并赋值给rs第1章数据库基础知识10（3）一般利用循环操作记录集DoWhileNotrs.EOF...rs.MoveNextLoop（4）关闭并回收对象所占内存rs.Close'关闭记录集db．Close'关闭数据库Setrs=Nothing'释放rs对象内存空间Setdb=Nothing'释放db对象内存空间第1章数据库基础知识11编辑、添加、删除、移动记录操作第1章数据库基础知识12【例10.1】　利用DAO对象操作“学籍管理”数据库，将“学生”表中少数民族学生的高考成绩统一增加10分。PublicSubDAOTest()DimwsAsDAO.Workspace'定义Workspace对象DimdbAsDAO.Database'定义数据库对象DimrsAsDAO.Recordset'定义记录集对象Setws=DBEngine.Workspaces(0)'将默认工作空间赋值给ws'打开制定的数据库Setdb=ws.OpenDatabase("F:\写书实例\学籍管理.accdb")Setrs=dB．OpenRecordset("学生")'打开学生表，并将记录集赋值给rs'循环操作记录集DoWhileNotrs.EOFIfrs.Fields("mz")<>"汉族"Thenrs.Editrs.Fields("gkcj")=rs.Fields("gkcj")+10rs.UpdateEndIfrs.MoveNextLooprs.Close'关闭记录集dB．Close'关闭数据库Setrs=Nothing'释放rs对象内存空间Setdb=Nothing'释放db对象内存空间EndSub第1章数据库基础知识1310.3ActiveX数据对象10.3.1ADO库的引用方法10.3.2ADO模型层次结构10.3.3主要ADO对象的使用第1章数据库基础知识1410.3.1ADO库的引用方法其引用方法和DAO库的操作方法相同，当出现如图10.2所示的界面时，选中列表框中“MicrosoftActiveDataObjects2.1Library”前面的选项第1章数据库基础知识1510.3.2ADO模型层次结构（1）Connection对象用来建立与数据库的连接，相当于在应用程序和数据库中建立一条数据传输线。（2）Command对象用来对数据库执行命令，如查询、添加、删除、修改记录。（3）RecordSet对象用来得到从数据库返回的记录集。这个记录集是一个连接的数据库中的表，或者是Command对象执行结果返回的记录集。所有对数据的操作几乎都是在RecordSet对象中完成的，可以完成指定行、移动行、添加、删除和修改记录。（4）Field对象表示记录集中的字段数据信息。（5）Error对象表示程序出错时的扩展信息。第1章数据库基础知识1610.3.3主要ADO对象的使用1．Connection对象定义一个Connection对象的方法如下：DimcnnAsADODB.ConnectionConnection对象必须实例化后才可以使用，实例化方法如下：Setcnn=NewADODB.Connection第1章数据库基础知识17ConnectionString属性该属性指示用于建立到数据源的连接信息。常见的设置方法如下。设置数据库提供者的名称：cnn.Provider="Microsoft.ACE.OLEDB.12.0"'Access2010数据库提供者名称指定连接的数据库名称：cnn.ConnectionString="DataSource=F:\数据库实例\学籍管理.accdb"也可以通过DefaultDatabase属性指定Connection对象的默认数据库，如要连接“学籍管理”数据库，可以设置DefaultDatabase的属性值为：cnn.DefaultDatabase="F:\数据库实例\学籍管理.accdb"第1章数据库基础知识18Open方法可以调用open方法打开连接，方法如下：cnn.open如果Connection对象没有设置相关属性，也可以利用带参数选项的open方法打开连接，open方法的语法格式为：连接对象名.OpenConnectionString,UserId,Password,Options例如：cnn.Open"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=F:\数据库实例\学籍管理.accdb"第1章数据库基础知识19Close方法当连接不使用时，一定要断开连接，比如断开cnn对象的连接，其方法为：cnn.close第1章数据库基础知识20例10.2ADO编程连接教学管理数据库实例。PublicSubMyConnection()DimcnnAsADODB.Connection'声明connection对象Setcnn=NewADODB.Connection'实例化该对象'flag_begincnn.Provider="Microsoft.ACE.OLEDB.12.0"'Access2010数据库提供者的名称'指定连接的数据库名称cnn.ConnectionString="DataSource=F:\数据库实例\学籍管理.accdb"'flag_endcnn.Open'打开连接cnn.Close'关闭连接Setcnn=Nothing'撤销连接EndSub提示：Access数据库编程时往往要连接的是当前数据库，可以使用系统提供的当前活动连接CurrentProject.Connection。如：cnn.OpenCurrentProject.Connection第1章数据库基础知识2110.4　特殊域聚合函数和RunSQL方法1．特殊域聚合函数常用的特殊域聚合函数有DCount函数、DAvg函数、DSum函数、DMax函数、DMin函数和DLookup函数。（1）DCount函数格式：DCount(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的人数，语句为：n=DCount("[xh]","学生","[xb]='男'")第1章数据库基础知识22（2）DAvg函数可以使用DAvg函数来计算特定记录集内一组值的平均值。格式：DAvg(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的平均高考成绩，语句为：n=DAvg("[gkcj]","学生","[xb]='男'")（3）DSum函数可以使用DSum函数来计算特定记录集内一组值的总和。格式：DSum(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的高考成绩总和，语句为：n=DSum("[gkcj]","学生","[xb]='男'")第1章数据库基础知识23（4）DMax函数可以使用DMax函数来计算特定记录集内一组值的最大值。格式：DMax(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的高考成绩最高分，语句为：n=DMax("[gkcj]","学生","[xb]='男'")（5）DMin函数可以使用DMin函数来计算特定记录集内一组值的最小值。格式：DMin(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的高考成绩最低分，语句为：n=DMin("[gkcj]","学生","[xb]='男'")第1章数据库基础知识24（6）DLookup函数可以使用DSum函数来计算特定记录集内获取特定字段的值。格式：DLookup(表达式,记录集[,条件表达式])例如，获取“学生”表中刘航同学所在的班级，语句为：n=DLookup("[bjmc]","学生","[xm]='刘航'")第1章数据库基础知识25例如，窗体上有一个文本框控件（名称为txtXH），在该控件中输入学号，将来自于“学生”表中该学生的姓名显示在另一个文本框控件（名称为txtXM）中，语句为：Me!txtXM=DLookup("[xm]","学生","[xh]='"&Me!txtXH&"'")第1章数据库基础知识262．RunSQL方法DoCmd对象的RunSQL方法可以直接运行Access的操作查询，完成对数据表记录的操作。也可以运行数据定义语句实现表和索引的定义。格式为：RunSQL(SQLStatement,UseTransaction)例如，将所有学生的高考成绩增加10分，语句为：DoCmd.RunSQL"update学生setgkcj=gkcj+10"第1章数据库基础知识2710.5综合案例【例10.4】　对学籍管理数据库中的教师表进行操作，能够实现记录的前后移动以及数据库常用的增加、删除或修改记录的功能。其界面如图10.5所示。第1章数据库基础知识28界面布局如下：控件类型名称标题控件类型名称标题文本框txtgh工号：文本框txtxm姓名：文本框txtxb性别：文本框txtzc职称：命令按钮cmdFirst第一条命令按钮cmdPrev向上命令按钮cmdNext向下命令按钮cmdLast最后一条命令按钮cmdAdd添加命令按钮cmdDel删除命令按钮cmdModi修改命令按钮cmdExit退出第1章数据库基础知识29表10.7　窗体界面元素关键代码如下：OptionCompareDatabase'通用模块中声明并初始化ADO各个对象DimcnnAsNewADODB.ConnectionDimrstAsNewADODB.Recordset'增加命令按钮单击事件过程PrivateSubcmdAdd_Click()'工号为主键，不能够为空,先判断Iftxtgh<>""Thenrst.AddNew'增加空白记录rst("gh")=Me!txtgh'将添加的内容保存在记录集中rst("xm")=Me!txtxmrst("xb")=Me!txtxbrst("zc")=Me!txtzcrst.Update'更新数据库ElseMsgBox"工号为主键不能够为空！"EndIfEndSub第1章数据库基础知识30'删除命令按钮单击事件过程PrivateSubcmdDel_Click()rst.DeleteEndSub'退出命令按钮单击事件过程PrivateSubcmdExit_Click()rst.Closecnn.CloseSetrst=NothingSetcnn=NothingDoCmd.CloseEndSub第1章数据库基础知识31'第一条命令按钮单击事件过程PrivateSubcmdFirst_Click()rst.MoveFirstCallshowRecordEndSub'最后一条命令按钮单击事件过程PrivateSubcmdLast_Click()rst.MoveLastCallshowRecordEndSub'修改命令按钮单击事件过程PrivateSubcmdModify_Click()Iftxtgh<>""Thenrst("gh")=Me!txtgh'将添加的内容保存在记录集中rst("xm")=Me!txtxmrst("xb")=Me!txtxbrst("zc")=Me!txtzcrst.Update'更新数据库ElseMsgBox"工号为主键不能够修改为空！"EndIfEndSub'向下命令按钮单击事件过程PrivateSubcmdNext_Click()rst.MoveNextIfrst.EOFThenrst.MoveLastEndIfCallshowRecordEndSub'向上命令按钮单击事件过程PrivateSubcmdPrev_Click()rst.MovePreviousIfrst.BOFThenrst.MoveFirstEndIfCallshowRecordEndSub'窗体加载事件过程PrivateSubForm_Load()DimstrSqlAsStringcnn.Provider="Microsoft.ACE.OLEDB.12.0"cnn.ConnectionString="F:\数据库实例\学籍管理.accdb"cnn.Open'选择教师的工号、姓名、性别和职称四个字段strSql="selectgh,xm,xb,zcfrom教师"'需要向前向后移动记录，所以不能使用默认的游标adOpenForwardOnlyrst.OpenstrSql,cnn,adOpenDynamic,adLockOptimisticCallshowRecordEndSub'标准过程，将记录集通过窗体界面显示数据PublicSubshowRecord()Me!txtgh=rst("gh")Me!txtxm=rst("xm")Me!txtxb=rst("xb")Me!txtzc=rst("zc")EndSub第1章数据库基础知识32