第8章-VBA数据库编程

第8章

VBA数据库编程主编：潘惠勇

夏敏捷Access数据库技术及应用1教学目标了解数据库引擎的作用。了解Access2016访问数据库的几种接口技术。了解DAO数据库编程方法。掌握ADO数据库编程的方法。2第8章-VBA数据库编程第8章VBA数据库编程8.1VBA数据库编程相关基础知识8.2

数据访问对象8.3ActiveX数据对象8.4特殊域聚合函数和RunSQL方法8.5综合案例3第8章-VBA数据库编程8.1VBA数据库编程相关基础知识8.1.1VBA数据库应用程序一般框架8.1.2VBA数据库访问接口8.1.3VBA访问的数据类型4第8章-VBA数据库编程8.1.1VBA数据库应用程序一般框架它由3部分组成:

应用程序界面是用户和应用程序的接口，用来输入和展现数据信息；

数据库负责数据的存储；

数据库引擎实际上是一组动态链接库（DLL），当程序运行时被连接到VBA程序而实现对数据库数据的访问功能5第8章-VBA数据库编程8.1.2VBA数据库访问接口（1）开放数据库互连应用编程接口（OpenDatabaseConnectivityAPI，ODBCAPI）（2）数据访问对象（DataAccessObject，DAO）（3）ActiveX数据对象（ActiveXDataObjects，ADO）6第8章-VBA数据库编程8.1.3VBA访问的数据类型VBA访问的数据库有3种：（1）本地数据库本地数据库文件格式与MicrosoftAccess相同。Jet引擎直接创建和操作这些数据库。（2）外部数据库

访问符合“索引顺序访问文件方法（ISAM）”数据库，包括dBaseIII、dBaseIV、Foxpro2.0和2.5以及Paradox3.x和4.x。（3）ODBC数据库

访问符合ODBC标准的客户机/服务器数据库，如MicrosoftSQLServer。7第8章-VBA数据库编程8.2数据访问对象8.2.1DAO库的引用方法8.2.2DAO模型层次结构8.2.3利用DAO访问数据库的一般步骤8第8章-VBA数据库编程8.2.1DAO库的引用方法

将“MicrosoftOffice16.0AccessdatabaseengineObjectLibrary”选项选中，在Windows10环境下，默认该项引用是选中的。9第8章-VBA数据库编程8.2.2DAO模型层次结构10第8章-VBA数据库编程①DBEngine对象：表示数据库引擎，包含并控制模型中的其他对象。②WorkSpace对象：表示工作区。③DataBase对象：表示操作的数据库对象。④RecordSet对象：表示数据操作返回的记录集，可以来自于表、查询或SQL语句的运行结果。⑤Field对象：代表在数据集中的某一列。⑥QueryDef对象：表示数据库查询信息。⑦Error对象：包含使用DAO对象产生的错误信息。8.2.3利用DAO访问数据库的一般步骤（1）首先定义各个对象变量DimwsAsDAO.Workspace '定义Workspace对象DimdbAsDAO.Database '定义数据库对象DimrsAsDAO.Recordset '定义记录集对象（2）为各个对象赋值Setws=DBEngine.Workspaces(0) '将默认工作空间赋值给wsSetdb=ws.OpenDatabase("需要打开的数据库") '打开指定的数据库Setrs=db.OpenRecordset(<表、查询、SQL语句>) '打开指定记录集并赋值给rs11第8章-VBA数据库编程3）一般利用循环操作记录集DoWhileNotrs.EOF...rs.MoveNextLoop（4）关闭并回收对象所占内存rs.Close '关闭记录集db．Close '关闭数据库Setrs=Nothing '释放rs对象内存空间Setdb=Nothing '释放db对象内存空间12第8章-VBA数据库编程编辑、添加、删除、移动记录操作13第8章-VBA数据库编程【例8.1】利用DAO对象操作“学籍管理”数据库，将“学生”表中非汉族学生的民族修改为少数民族14第8章-VBA数据库编程分析：利用DAO对象打开“学生”表，从第一条记录开始判断该学生是否为汉族，如果不是，则将其“民族”设置为“少数民族”，然后将记录指针向下移动一条记录，继续判断，直至记录指针移动到记录集的末尾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("mz")="少数民族"rs.UpdateEndIfrs.MoveNextLooprs.Close '关闭记录集dB．Close '关闭数据库Setrs=Nothing '释放rs对象内存空间Setdb=Nothing '释放db对象内存空间EndSub15第8章-VBA数据库编程8.3ActiveX数据对象8.3.1ADO库的引用方法8.3.2ADO模型层次结构8.3.3主要ADO对象的使用16第8章-VBA数据库编程8.3.1ADO库的引用方法其引用方法和DAO库的操作方法相同，当出现如图8.2所示的界面时，选中列表框中“MicrosoftActiveDataObjects2.1Library”前面的选项17第8章-VBA数据库编程8.3.2ADO模型层次结构18第8章-VBA数据库编程（1）Connection对象用来建立与数据库的连接，相当于在应用程序和数据库中建立一条数据传输线。（2）Command对象用来对数据库执行命令，如查询、添加、删除、修改记录。（3）RecordSet对象用来得到从数据库返回的记录集。这个记录集是一个连接的数据库中的表，或者是Command对象执行结果返回的记录集。所有对数据的操作几乎都是在RecordSet对象中完成的，可以完成指定行、移动行、添加、删除和修改记录。（4）Field对象表示记录集中的字段数据信息。（5）Error对象表示程序出错时的扩展信息。8.3.3主要ADO对象的使用1．Connection对象定义一个Connection对象的方法如下：DimcnnAsADODB.ConnectionConnection对象必须实例化后才可以使用，实例化方法如下：Setcnn=NewADODB.Connection19第8章-VBA数据库编程ConnectionString属性该属性指示用于建立到数据源的连接信息。常见的设置方法如下。

设置数据库提供者的名称：cnn.Provider="Microsoft.ACE.OLEDB.16.0"'Access2016数据库提供者名称

指定连接的数据库名称：cnn.ConnectionString="DataSource=F:\数据库实例\学籍管理.accdb"也可以通过DefaultDatabase属性指定Connection对象的默认数据库，如要连接“学籍管理”数据库，可以设置DefaultDatabase的属性值为：cnn.DefaultDatabase="F:\数据库实例\学籍管理.accdb"20第8章-VBA数据库编程Open方法可以调用open方法打开连接，方法如下：cnn.open如果Connection对象没有设置相关属性，也可以利用带参数选项的open方法打开连接，open方法的语法格式为：连接对象名.OpenConnectionString,UserId,Password,Options例如：cnn.Open"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=F:\数据库实例\学籍管理.accdb"21第8章-VBA数据库编程Close方法当连接不使用时，一定要断开连接，比如断开cnn对象的连接，其方法为：cnn.close22第8章-VBA数据库编程【例8.2】利用ADO编程连接“学籍管理”数据库实例。在标准模块下建立一个过程MyConnection，代码如下：PublicSubMyConnection()DimcnnAsADODB.Connection'声明Connection对象Setcnn=NewADODB.Connection'实例化该对象'flag_begincnn.Provider="Microsoft.ACE.OLEDB.16.0"'Access2016数据库提供者的名称'指定连接的数据库名称cnn.ConnectionString="DataSource=F:\数据库实例\学籍管理.accdb"'flag_endcnn.Open'打开连接cnn.Close'关闭连接Setcnn=Nothing'撤销连接EndSub活动连接CurrentProject.Connection，即将注释flag_begin和flag_end之间的语句修改为：cnn.OpenCurrentProject.Connection23第8章-VBA数据库编程2．Command对象ADO的Command对象用来执行对数据源的请求，获得数据集。Command对象也需要先定义，方法如下：DimcmdAsADODB.Command'声明Command对象Setcmd=NewADODB.Command'实例化该对象Command对象常用属性有ActiveConnection和CommandText属性。ActiveConnection属性用来指明Command对象所要关联的连接对象；CommandText属性指明查询命令的文本内容，可以是一个表，也可以是SQL语句。设置完Command对象的ActiveConnection和CommandText属性后，最后调用Command的Execute方法返回所需要的记录集。Execute方法的语法结构如下。Setrecordset=cmd.Execute(RecordsAffected,Parameters,Options)这三个参数可以省略24第8章-VBA数据库编程【例8.3】利用ADO编程，将学生高考成绩统一加10分。25第8章-VBA数据库编程分析：可以将Command对象的CommandText属性设置为SQL语句，建立当前数据库的连接，然后执行Command对象的无返回记录集的Execute方法，代码如下。PublicSubMyCommand()DimcmdAsADODB.CommandSetcmd=NewADODB.Command'将当前连接作为cmd对象的活动连接cmd．ActiveConnection=CurrentProject.Connection'将CommandText设置为SQL语句cmd.CommandText="update学生setgkcj=gkcj+10"'执行不返回记录集的操作cmd.ExecuteEndSub运行该过程，然后打开“学生”表，会发现高考成绩已经增加了10分。3．Recordset对象RecordSet对象是最常用的一个ADO对象，从后台数据库中查询所需要的记录就存放在记录集中。记录集由行和列组成，像二维表一样，利用RecordSet对象的相关属性和方法可以对记录集中的数据进行查询、增加、修改、删除等操作。RecordSet对象的定义如下。

DimrstAsADODB.Recordset

Setrst=NewADODB.Recordset定义完记录集对象rst后，下面就要为对象获取所要的记录集。获取的方法很多，下面介绍几种常用的方法。26第8章-VBA数据库编程3．Recordset对象（1）通过Connection对象的Execute方法获得语法格式为：Setrecordset=Connection.Execute（CommandText,RecordsAffected,Options)参数说明如下。CommandText：字符串值，包含要执行的SQL语句、存储过程、URL或提供程序特定文本，还可以使用表名称。RecordsAffected：可选，一个Long型变量，提供程序向其返回操作所影响的记录数。Options：可选，长整型值，用于指示提供程序评估CommandText参数的方式。PublicSubrstByConnection()DimcnnAsNewADODB.Connection '声明的时候直接实例化对象Setcnn=CurrentProject.ConnectionDimrstAsNewADODB.Recordset '声明的时候直接实例化对象Setrst=cnn.Execute("select*from教师") '返回记录集Debug.Printrst.Fields(0),rst.Fields(1),rst.Fields(2)cnn.CloseEndSub该例获取教师表中的所有数据，存储到记录集rst对象中，游标当前处于第一条记录，然后在立即窗口打印出该条记录的前3个字段的信息。这里Fields是RecordSet对象下的对象集合，包括了它的所有对象，通过Fields对象可以访问记录集中的各个字段，Fields(0)代表第一个字段，fields(1)代表第二个字段，依此类推。27第8章-VBA数据库编程3．Recordset对象（2）通过Command对象的Execute方法获得例8.3中执行的是Command对象的不返回记录集的Execute方法，实际编程中通过Command对象的Execute方法返回记录集是获得记录集最常用的方法。PublicSubrstByCommand()DimcnnAsNewADODB.Connection'声明的时候直接实例化对象DimcmdAsNewADODB.CommandDimrstAsNewADODB.Recordsetcnn.Provider="Microsoft.ACE.OLEDB.16.0"cnn.ConnectionString="F:\数据库实例\学籍管理.accdb"cnn.Opencmd.ActiveConnection=cnncmd.CommandText="select*from教师"Setrst=cmd.Execute'返回记录集Debug.Printrst("gh"),rst("xm"),rst("xb")cnn.CloseEndSub这里通过rst("字段名")方法也可以获得记录集中各个字段的值。28第8章-VBA数据库编程3．Recordset对象（3）通过Recordset对象的Open方法获得语法格式为：recordset.OpenSource,ActiveConnection,CursorType,LockType,Options参数说明如下。Source：可选，变量型，取值为有效的Command对象、SQL语句、表名称等。ActiveConnection：可选，取值为有效的Connection对象变量名称的变量型，或包含ConnectionString参数的字符串型。CursorType：可选，返回CursorTypeEnum值，用于确定在打开Recordset时提供程序应使用的游标的类型。CursorType参数详解如表8.2所示29第8章-VBA数据库编程30第8章-VBA数据库编程举例PublicSubcmdConnection()DimcnnAsNewADODB.Connection'声明的时候直接实例化对象DimrstAsNewADODB.RecordsetDimstrSQLAsStringcnn.Provider="Microsoft.ACE.OLEDB.16.0"cnn.ConnectionString="F:\数据库实例\学籍管理.accdb"cnn.OpenstrSQL="select*from教师"rst.OpenstrSQL,cnn,adOpenForwardOnly'获得记录集Debug.Printrst.GetStringcnn.CloseEndSub31第8章-VBA数据库编程3．Recordset对象Recordset对象常用的属性和方法32第8章-VBA数据库编程表8.4 RecordSet对象常用属性属性说明Bof若为True，记录指针指向记录集的顶部（即指向第一个记录之前）Eof若为True，记录指针指向记录集的底部（即指向最后一个记录之后）RecordCount返回记录集对象中的记录个数表8.5 RecordSet对象常用方法方法说明Open打开一个RecordSet对象Close关闭一个RecordSet对象Update将RecordSet对象中的数据保存（即写入）到数据库Delete删除RecordSet对象中的一个或多个记录Find在RecordSet中查找满足指定条件的行MoveFirst把记录指针移到第一个记录MoveLast把记录指针移到最后一个记录MoveNext把记录指针移到下一个记录MovePrevious把记录指针移到前一个记录8.4特殊域聚合函数和RunSQL方法1．特殊域聚合函数常用的特殊域聚合函数有DCount函数、DAvg函数、DSum函数、DMax函数、DMin函数和DLookup函数。

（1）DCount函数格式：DCount(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的人数，语句为：n=DCount("[xh]","学生","[xb]='男'")33第8章-VBA数据库编程（2）DAvg函数可以使用DAvg函数来计算特定记录集内一组值的平均值。格式：DAvg(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的平均高考成绩，语句为：n=DAvg("[gkcj]","学生","[xb]='男'")（3）DSum函数可以使用DSum函数来计算特定记录集内一组值的总和。格式：DSum(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的高考成绩总和，语句为：n=DSum("[gkcj]","学生","[xb]='男'")34第8章-VBA数据库编程（4）DMax函数可以使用DMax函数来计算特定记录集内一组值的最大值。格式：DMax(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的高考成绩最高分，语句为：n=DMax("[gkcj]","学生","[xb]='男'")（5）DMin函数可以使用DMin函数来计算特定记录集内一组值的最小值。格式：DMin(表达式,记录集[,条件表达式])例如，计算“学生”表中男同学的高考成绩最低分，语句为：n=DMin("[gkcj]","学生","[xb]='男'")35第8章-VBA数据库编程（6）DLookup函数可以使用DSum函数来计算特定记录集内获取特定字段的值。格式：DLookup(表达式,记录集[,条件表达式])例如，获取“学生”表中刘航同学所在的班级，语句为：n=DLookup("[bjmc]","学生","[xm]='刘航'")36第8章-VBA数据库编程例如，窗体上有一个文本框控件（名称为txtXH），在该控件中输入学号，将来自于“学生”表中该学生的姓名显示在另一个文本框控件（名称为txtXM）中，语句为：Me!txtXM=DLookup("[xm]","学生","[xh]='"&Me!txtXH&"'")37第8章-VBA数据库编程2．RunSQL方法DoCmd对象的RunSQL方法可以直接运行Access的操作查询，完成对数据表记录的操作。也可以运行数据定义语句实现表和索引的定义。格式为：RunSQL(SQLStatement,UseTransaction)例如，将所有学生的高考成绩增加10分，语句为：DoCmd.RunSQL"update学生setgkcj=gkcj+10"38第8章-VBA数据库编程8.5综合案例39第8章-VBA数据库编程【例8.4】对学籍管理数据库中的教师表进行操作，能够实现记录的前后移动以及数据库常用的增加、删除或修改记录的功能。其界面如图8.5所示。界面布局如下：40第8章-VBA数据库编程控件类型名称标题控件类型名称标题文本框txtgh工号：文本框txtxm姓名：文本框txtxb性别：文本框txtzc职称：命令按钮cmdFirst第一条命令按钮cmdPrev向上命令按钮cmdNext向下命令按钮cmdLast最后一条命令按钮cmdAdd添加命令按钮cmdDel删除命令按钮cmdModi修改命令按钮cmdExit退出关键代码如下：41第8章-VBA数据库编程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'删除命令按钮单击事件过程PrivateSubcmdDel_Click()rst.DeleteEndSub'退出命令按钮单击事件过程PrivateSubcmdExit_Click()rst.Closecnn.Close