编程与数据库

一、 数据库引擎及其接口

数据库引擎是一组动态链接库DLL （Dynamic Link Library）， 在程序运行时被连接到VBA， 实现对数据库的数据访问功能。 它是应用程序与物理数据库之间的桥梁。 VBA中可以使用访问数据库的三种接口：

（1） ODBCAPI （Open Data Base Connectivity API）： “开放式数据库连接”， 是一种关系数据源的接口界面。 ODBC基于SQL （Structured Query Language）， 把SQL作为访问数据库的标准， 一个应用程序通过一组通用代码访问不同的数据库管理系统。

使用ODBC方法进行连接数据库时， 微软提供了开放服务结构， 并建立了一组对数据库的访问标准API， 这些API利用SQL来完成其大部分任务。 目前操作系统支持VBA、 C语言、 Java等多种语言连接数据库， 这些程序要访问一个数据库， 首先必须用ODBC管理器注册一个数据源， 管理器根据数据源提供的数据库位置、 数据库类型及ODBC驱动程序等信息， 建立起ODBC与具体数据库的联系。 只要应用程序将数据源名提供给ODBC， ODBC就能建立起与相应数据库的连接， 其使用步骤如下：

•设置连接字符串。

•实例化Command连接对象。

•执行Open方法打开连接。

•执行SQL语句。

•将查询操作结果赋给Grid View， 将输出表格中的一行数据源。

•绑定Grid View。

•关闭连接。

（2） DAO （Data Access Objects）： “数据访问对象”， 是一种面向对象的界面接口， 提供一个访问数据库的对象模型， 用其中定义的一系列数据访问对象， 实现对数据库的各种操作。 使用DAO的程序编码非常简单。 VBA通过DAO和数据引擎既可以识别Access本身的数据库， 也可以识别外部数据库， 如VFP （Visual Fox Pro）、 文本文件数据库和Microsoft Excel或Lotus1－2－3电子表格等。

DAO模型是设计关系数据库系统结构的对象类的集合， 它们提供了管理关系型数据库系统所需的全部操作的属性和方法， 这其中包括对数据库连接、 创建定义表、 字段和索引命令、 建立表之间的关系、 定位和查询数据库等。 其使用步骤如图9.48所示。

图9.48 数据库连接操作

（3） ADO （Active XData Objects）： “Active数据对象”， 是基于组件的数据库编程接口。ADO实际是一种提供访问各种数据类型的连接机制， 是一个与编程语言无关的COM （Component Object Model） 组件系统。 ADO设计为一种极简单的格式， 可以方便地连接任何符合ODBC标准的数据库。 其使用步骤如下：

•使用Connection对象建立与数据库的连接。

•使用Command对象设置命令参数并发出命令。

•使用Record Set对象存储数据操作返回的记录集。

•使用Field对象对记录集中的字段数据进行操作， 包括： 定义和创建ADO对象实例变量、 返回Select语句记录集、 采用Delete （删除）、 Update （更新）、 Insert （插入） 记录操作。

•关闭、 回收相关对象。

二、 使用ADO连接数据库

1. 设置ADO连接

使用ADO连接数据库时， 需要单击 【工具】 菜单下的 “引用”， 在可使用的引用中将Microsoft Active XData Objects2.6选中， 如图9.49所示。

图9.49 数据库连接操作

2. 定义和创建ADO对象实例变量

•Dimcnn As New ADODB.Connection。

•Dimcm As New ADODB.Command。

•Dimrs As New ADODB.Record Set。

•Dimfd As ADODB.Field。

3. 打开数据库连接

cnn.Open[Connection Stirng][,User ID][,Pass Word][,Open Options]

其中：

•Connection Stirng——数据库连接字符串。

•User ID——用户名。

•Pass Word——密码。

•Open Options——可选项。

数据库连接字符串的具体写法， 根据使用的编程接口的不同， 分为两种： ODBC、 OLE DB。 获得当前数据的连接语句为：

Setcnn=Current Project.Connection

4. 向数据库连接发送SQL语句

有三种方法发送方式：

（1） Record Set对象的Open方法语句：

rs.Open[Source][,Active Connection][,Cursor Type][,Lock Type]

其中：

•Source——数据源。

•Active Connection——数据库连接。

•Cursor Type——游标类型。

•Lock Type——锁定类型。

【说明】

•Selece语句返回记录集， Delete、 Update、 Insert不返回记录集。

•类型： ad Open Dynamic——动态游标， 可以修改数据； ad Open Static——静态游标， 只能查看数据。

•锁定类型： ad Lock Read Only——只读锁定， 只能查看数据； ad Lock Optimstic——保守式锁定， 可以修改数据， 在编辑数据时即锁定数据源记录， 直到数据编辑完成才释放。ad Lock Batch Optimistic——开放式更新， 应用于成批更新模式。

例如： rs.openstr SQL,cn,ad Open Dynamic,ad Lock Optimistic,ad Cmd Text

【说明】

•str SQL——数据集。

•cn——声明的Connection对象。

•ad Open Dynamic——动态打开方式。

•ad Lock Optimistic——保守式锁定。

•ad Cmd Text——打开的对象。

（2） 使用Connection对象的Execute方法：

返回记录集： Setrs＝cnn.Execute （SQL语句）。

不返回记录集： cnn.Execute （SQL语句）。

（3） 使用Command对象的Execute方法：

返回记录集： Setrs＝cmm.Execute（）。

不返回记录集： cmm.Execute（）。

5. 操作记录集

使用Record Set的方法：

•定位记录： Move方法。

•检索记录： Find和Seek方法。

•添加记录： Add New方法。

•更新记录： Up Date方法。

•删除记录： Delete方法。

•Record Set： 记录集对象记录指针的移动方法。

•Move First： 记录指针移到第一条记录。

•Move Next： 记录指针移到当前记录的下一条记录。

•Move Previous： 记录指针移到当前记录的上一条记录。

•Move Last： 记录指针移到最后一条记录。

【说明】

Record Set记录集的BOF和EOF属性用于判断记录指针是否处于有记录的正常位置。

•记录指针将指向最后一条记录之后， EOF属性为True。

•记录指针将指向第一条记录之前， BOF属性为True。

•BOF和EOF属性的值均为True时， 表示记录集为空。

例32： 若记录集的EOF属性为True， 则回到首记录：

Private Sub Command3_Click()

rs Teacher.Move Next

Ifrs Teacher.EOFThen

rs Teacher.Move First

End If

End Sub

例33： 若记录集的EOF属性为True， 则回到末记录：

Private Sub Command3_Click()

rs Teacher.Move Next

Ifrs Teacher.EOFThen

rs Teacher.Move Last

End If

End Sub

6. 关闭、 回收相关对象

语句：

rs.Close——关闭操作。

cnn.Close——关闭连接。

Setrs＝Nothing——释放操作。

Setcnn＝Nothing——释放连接。

三、 利用DAO实现访问数据库表

例34： 使用DAO技术， 完成对 “student.ACCDB” 表学生年龄加1操作。 其代码如下：

Sub Set Age Plus1()

dimweas DAO.Workspace '工作区对象

dimdbas DAO.Database '数据库对象

dimrsas DAO.Recordset '记录集对象

dimfdas DAO.Field '字段对象

setws=DBEngine.Workspace(0)

setdb=ws.Open Database(“.\student.accdb”) '打开数据库

setrs=db.Open Record Set(“学生表”) '返回“学生表”记录集 setfd=rs.Fields(“年龄”) '设置“年龄”字段

Dowhilenotrs.eof '对记录集用循环结构进行遍历

rs.edit '设置为“编辑”状态

fd=fd+1 '年龄+1

rs.update '更新记录，保存年龄值

rs.movenext '记录指针移动至下一条

Loop '关闭并回收对象变量

rs.close

db.close

setrs=Nothing

setdb=Nothing

Endsub

四、 利用ADO技术实现访问数据库表

例35： 使用ADO技术， 完成对 “student.ACCDB” 表学生年龄加1操作。 其代码如下：Sub Set Age Plus1()

dimcnas New ADODB.Connection '连接对象

dimrsas New ADODB.Record Set '记录集对象

dimfsas ADODB.Field '字段对象

dimstr Connectas String '连接字符串

dimstr SQLas String '查询字符串

strconnect=“.\student.accdb” '连接数据库

cn.Provider=“Microsoft.jet.oledb.4.0” '设置数据提供者 cn.openstrconnect '打开与数据源的连接

str SQL=“select年龄fromstudent” '设置查询语句

rs.openstr SQL,cn,ad Open Dynamic,ad Lock Optimistic,ad Cmd Text setfd=rs.Fields(“年龄”)

Dowhilenotrs.eof '对记录集用循环结构进行遍历fd=fd+1 '年龄+1

rs.update '更新记录，保存年龄值

rs.movenext '记录指针移动至下一条

Loop

rs.close '关闭并回收对象变量

db.close

setrs=Nothing

setdb=Nothing

Endsub

五、 案例十五的操作步骤 （二）

使用案例十五登录信息框的设计界面， 根据已有用户表9.23验证登录信息， 验证结果如图9.50所示。

表9.23 用户表

图9.50 数据库表验证结果

Private Sub Command7_Click()

Dimcn As New ADODB.Connection

Dimrs As New ADODB.Recordset

Dimstr As String

Setcn=Current Project.Connection '建立本地连接

rs.Active Connection=cn

logname=Trim(Me.username)

pass=Trim(Me.pass)

If Is Null(username.Value)Then

Msg Box"用户名不能为空，请输入用户名！"

Else If Is Null(pass.Value)Then

Msg Box"密码不能为空，请输入密码！"Else

str="SELECT∗FROMpws WHERE用户名='"&logname&"'AND密码='"&pass&"'"

rs.Openstr,cn,ad Open Dynamic,ad Lock Optimistic Ifrs.EOFThen

Msg Box"用户名或密码错误，请重新输入！"

Me.username.Text= ""

Me.pass.Text= ""Else

Setfd=rs.Fields("权限")

Iffd= "管理员"Then

Msg Box"欢迎，你以管理员权限登录，可以管理数据库表！"Else

Do Cmd.Close

Do Cmd.Open Form"综合管理"

Msg Box"欢迎，你以普通用户登录，只有查询权限" End If

End If

End If

End Sub

当用户名或密码输入错误时， 结果如图9.51所示。

当用户名或密码输入为空时， 结果分别提示如图9.52所示。

图9.51 错误验证结果

图9.52 输入为空验证结果