chap09VBA数据库编程技术

第9章VBA数据库编程技术9.1数据访问接口ADO9.2ADO主要对象9.3VBA在Access中的数据库编程实例第9章本章小结思考与练习本章首先概述数据访问接口，接着将详细介绍ADO三个主要对象，最后通过实例说明VBA使用ADO对象访问Access数据库的具体方法与编程技巧。本章的知识结构导航图如图9-1所示。9.1数据访问接口ADO9.1.1数据库引擎和接口所谓数据库引擎实际上是一组动态链接库〔DLL〕，当VBA程序运行时被连接到应用程序从而实现对数据库的数据的访问功能。数据库引擎〔DatabaseEngine〕是应用程序与物理数据库之间的桥梁，是一种通用接口的方式，用户可以使用统一形式和相同的数据访问与处理方法来访问各种类型数据库。在VBA中主要提供了3种数据库访问接口：9.1.1数据库引擎和接口1.ODBCAPI〔OpenDatabaseConnectivityAPI，开放数据库互连应用程序接口〕ODBC为关系数据库编程提供统一的接口，用户可通过它对不同类型的关系数据库进行操作。但是由于ODBCAPI允许对数据库进行比较接近底层的配置和控制，属底层数据库接口，在Access应用中，要直接使用ODBCAPI访问数据库那么需要大量VBA函数原型声明和一些繁琐的、底层的编程。9.1.1数据库引擎和接口2.DAO〔DataAccessObjects，数据访问对象〕DAO既提供了一组基于功能的API函数，也提供了一个访问数据库的对象模型，在Access数据库应用程序中，开发者可利用其中定义的如Database、QueryDef、RecordSet等一系列数据访问对象，实现对数据库的各种操作。9.1.1数据库引擎和接口3.ADO〔ActiveXDataObjects，动态数据对象〕ADO是基于组件的数据库编程接口，它是一个与编程语言无关的COM组件系统，可以对来自多种数据提供者的数据进行操作。由于ADO是对微软所支持的数据库进行操作的最有效和最简单直接的方法。9.1.2ADOADO以OLEDB为根底，对OLEDB底层操作的复杂接口进行封装，使应用程序通过ADO极其简单的COM接口，就可以访问来自OLEDB数据源的数据，其中不管是关系数据库还是非关系数据库，也不管是本地数据库还是远程数据库，应用程序、ADO和数据源之间关系如图9-2所示。9.1.2ADO图9-2应用程序通过ADO访问不同数据源9.1.2ADO与其他数据访问接口相比，ADO具有以下一些优点：1.ADO能够访问各种支持OLEDB的数据源，包括数据库和文本文件、电子表格、电子邮件等数据源。2.ADO采用了ActiveX技术，与具体的编程语言无关，任何使用如VC++、Java、VB、Delphi等高级语言编写的应用程序都可以使用ADO来访问各类数据源。3.ADO将访问数据源的复杂过程抽象成几个易于理解的具体操作，并由实际对象来完成，因而使用起来简单方便。9.1.2ADO4.ADO对象模型简单易用，速度快，资源开销和网络流量少，在应用程序和数据源之间使用最少的层数，为应用程序和数据源之间提供了轻便、快捷、高性能的接口。5.ADO属应用层〔高层〕的编程接口，也可以在各种脚本语言〔Script〕中直接使用，特别适合于各种客户机/效劳器应用系统和基于Web的应用，尤其在脚本语言中访问Web数据库是ADO的一大优势。9.2ADO主要对象9.2.1ADO对象模型ADO对象模型是对ADO对象集合的完整概括，它能够更加灵活、有效地发挥ADO对象的功能特性。ADO对象模型如图9-3所示，主要包含Connection、Recordset、Command、Parameter、Field、Property和Error共7个对象，ADO对象模型中有关对象、集合的说明见表9-1。Connection、Recordset和Command是ADO对象模型中的三个最核心对象，也是应用程序访问数据源时使用最多的三个对象。9.2.1ADO对象模型图9-3ADO对象模型9.2.1ADO对象模型9.2.2Connection对象Connection〔连接〕对象用于建立应用程序与指定数据源的连接。使用Connection对象实现与指定数据源连接的根本步骤为：〔1〕创立Connection对象；〔2〕设置Connection对象的连接字符串ConnectionString属性，用以指示要连接的数据源信息；〔3〕翻开Connection对象，实现应用程序与数据源的物理连接；9.2.2Connection对象〔4〕为节省系统资源，待对数据源中数据操作结束后，应关闭Connection对象，实现应用程序与数据源的物理断开。要建立应用程序与数据源的连接，首先要在应用程序中声明一个Connection对象，并对其进行初始化，具体方法如下：DimconnAsADODB.Connection

Setconn=NewADODB.Connection创立完Connection对象，在连接数据源之前，可利用Connection对象的ConnectionString属性设置建立连接的数据源信息。设置ConnectionString属性的语法如下：9.2.2Connection对象连接对象变量.ConnectionString="参数1=参数1值;参数2=参数2值;……“接着就可以用Connection对象的Open方法实现应用程序与数据源的物理连接。Open方法的语法如下：连接对象变量.OpenConnectionString,UserID,Password待数据源中数据操作结束后，应关闭Connection对象，实现应用程序与数据源的物理断开，以节省系统资源的开销。关闭Connection对象应使用Close方法，语法如下：连接对象变量.Close9.2.2Connection对象需要说明的是，使用Close方法只是关闭应用程序与数据源的物理连接，而Connection对象并未从内存中释放，要从内存中释放已有Connection对象应用如下语法：Set连接对象变量=Nothing9.2.3Recordset对象Recordset〔记录集〕对象用于存储来自数据库中根本表或命令执行结果的记录全集。Recordset对象中的数据在逻辑上由每行的记录和每列的字段组成，每个字段又表示为一个Field对象。任一时候，Recordset对象所指的当前记录均为记录全集中的单个记录。类似于Connection对象，在使用Recordset对象之前，也应声明并初始化一个Recordset对象，方法如下：DimrsAsADODB.Recordset

Setrs=NewADODB.Recordset9.2.3Recordset对象创立一Recordset对象之后，就可以通过Recordset对象的Open方法获取数据源操作的记录集。Open方法语法如下：记录集对象变量.OpenSource,ActiveConnection,CursorType,LockType,Options9.2.3Recordset对象在数据库应用程序开发过程中，开发者可充分利用Recordset对象的属性或方法实现应用程序对记录集中的数据操作，Recordset对象中常用属性及方法有。1.ActiveConnection属性：通过设置ActiveConnection属性使翻开的数据源连接与Connection对象相关联，该属性值为有效的Connection对象变量或包含ConnectionString参数的连接字符串。2.AbsolutePosition属性：返回Recordset对象当前记录的序号位置。3.RecordCount属性：返回Recordset对象中记录的数目。9.2.3Recordset对象4.BOF、EOF属性：假设当前位置在Recordset对象第一条记录之前，BOF属性值为真，否那么为假；假设当前位置在Recordset对象最后一条记录之后，EOF属性值为真，否那么为假。5.AddNew方法：该方法将在Recordset对象中添加一条新记录。语法如下：记录集对象变量.AddNew6.Delete方法：该方法将删除Recordset对象中当前记录，语法如下：记录集对象变量.Delete9.2.3Recordset对象7.Update方法：该方法将使Recordset对象中当前记录的更新内容保存到数据库中，语法如下：记录集对象变量.Update8.Move、MoveFirst、MoveLast、MoveNext、MovePrevious方法：当翻开一个非空Recordset对象时，当前记录总是定位在第一条记录，可根据需要使用这些方法灵活地将记录指针移动到指定位置，记录定位语法见表9-6。9.2.3Recordset对象9.2.3Recordset对象9.Close方法：该方法用以关闭一个已翻开的Recordset对象，并且释放相关的数据和为该Recordset对象所申请的资源。语法如下：记录集对象变量.Close由于使用Close方法只释放相关的系统资源，Recordset对象并未从内存中释放，假设还需要翻开该Recordset对象，可直接使用该对象的Open方法。假设要将Recordset对象从内存中完全释放，应设置Recordset对象为Nothing，语法如下：Set记录集对象变量=Nothing9.2.4Command对象Command〔命令〕对象用以定义并执行针对数据源的具体命令，即通过传递指定的SQL命令来操作数据库，如建立数据表、删除数据表或修改数据表结构等；应用程序也可通过Command对象查询数据库，并将Command对象的运行结果返回给Recordset对象，以便进一步执行如增加、删除、更新、筛选记录等操作。9.2.4Command对象创立一个Command对象之后，就可利用该对象的属性及方法对数据源提出命令请求，Command对象的常用属性和方法有：1.ActiveConnection属性：通过设置ActiveConnection属性使已翻开的数据源连接与Connection对象相关联，ActiveConnection属性值可以是有效的Connection对象变量或包含ConnectionString参数的连接字符串。2.CommandText属性：用以表示Command对象要对数据源下达的命令，通常设置为能够完成某个特定功能的SQL语句、数据表或存储过程的调用等。3.Execute方法：这是Command对象的最主要方法，用以执行一个由CommandText属性指定的查询、SQL语句或存储过程。9.3VBA在Access中的9.3.1在VBA中引用ADO类库ADO是采用面向对象方法设计的，ADO各个对象的定义都被集中在ADO类库中。要使用ADO对象，首先要引用ADO类库。在VBA中引用ADO类库的操作步骤如下：1.在VBE环境中，选择“工具”菜单下的“引用”命令，翻开“引用”对话框，如图9-4所示。2.在“可使用的引用”列表框中选中“MicrosoftActiveXDataObjects2.1”复选框。另外，在这个对话框中还可以改变被引用类库的优先级。例如，选中“MicrosoftActiveXDataObjects2.1”这一类库，单击“优先级”的向上按钮，可提升ADO类库的优先级。9.3.1在VBA中引用ADO类库图9-4“引用”对话框9.3.2VBA数据库编程实例【例9-6】翻开“学生成绩管理.mdb”，设计课程成绩统计窗体，运行界面如图9-5所示，要求实现：1.在组合框Combo1中选择一课程编号，那么对应的课程名称、学分、任课教师、学时分别显示在对应文本框中。2.单击“统计”按钮，那么在对应文本框中显示指定课程的所有参考学生人数、课程平均分、60分以上人数和不及格人数。3.假设未指定具体课程编号就单击“统计”按钮，那么显示提示信息。9.3.2VBA数据库编程实例图9-5课程成绩统计窗体9.3.2VBA数据库编程实例设计步骤如下：〔1〕创立窗体并添加相关控件。控件的主要属性如表9-7所示。〔2〕利用组合框向导设置Combo1对象的值来源。设置步骤详见图9-6。〔3〕对组合框Combo1的Change事件编写如下代码：PrivateSubCombo1_Change()

DimrsAsADODB.Recordset

Setrs=NewADODB.Recordset

从"课程"和"教师"数据表中获取指定课程编号的记录9.3.2VBA数据库编程实例DimSQLstrAsString

SQLstr="Select课程.课程名称,课程.学时,课程.学分,教师.姓名From课程,教师Where课程.教师编号=教师.教师编号And课程.课程编号="&Me.Combo1&"'"

rs.OpenSQLstr,CurrentProject.Connection,2,2判断是否获取指定的记录IfNotrs.EOF()Then

Me.Text1=rs("课程名称")

Me.Text2=rs("学分")

Me.Text3=rs("姓名")

Me.Text4=rs("学时")

EndIf

rs.Close

Setrs=Nothing

EndSub9.3.2VBA数据库编程实例〔4〕对“统计”按钮Command1的Click事件编写如下代码：PrivateSubCommand1_Click()

DimrsAsADODB.Recordset

Setrs=NewADODB.Recordset从"成绩"数据表获取指定课程编号的所有记录DimSQLstrAsString

SQLstr="Select*From成绩Where课程编号="&Me.Combo1&"'"

rs.OpenSQLstr,CurrentProject.Connection,2,2判断获取的记录集是否为空,非空那么统计各项数据9.3.2VBA数据库编程实例IfNotrs.BOF()OrNotrs.EOF()Then

sum=0

n=0

x=0

y=0逐条记录累加成绩,并分别统计及格与不及格人数DoWhileNotrs.EOF()

sum=sum+rs("成绩")

n=n+1

Ifrs("成绩")>=60Then

x=x+1

Else

y=y+19.3.2VBA数据库编程实例EndIf

rs.MoveNext

Loop

aver=sum/n

Me.Text5=n

Me.Text6=Int(aver*100+05)/100

Me.Text7=x

Me.Text8=y

Else

未指定课程编号那么提示信息

MsgBox"课程编号为空,请重新指定课程编号“,0+16,"提示"

EndIf

rs.Close

Setrs=Nothing

EndSub【本章小结】本章在介绍Connection、Recordset和Command三个ADO主要对象的具体使用方法根底上，通过列举有针对性的实例〔如记录的查询、添加、更新、删除、记录中字段数据统计等〕