Transcript 第8章VBA数据库编程

第8章 VBA数据库编程
本章要点：
Docmd对象介绍
VBA数据库访问技术
综合案例
1
8.1 Docmd对象介绍
1. Docmd对象的调用格式
DoCmd.方法名 参数表
Docmd对象常用的操作方法
(1) 打开窗体—OpenForm方法
(2) 打开报表—OpenReport方法
(3) 打开查询—OpenQuery方法
(4) 打开表—OpenTable方法
(5) 打开模块—OpenModule方法
(6) 运行菜单或工具栏命令—RunCommand方法
(7) 运行宏—RunMacro方法
(8) 执行SQL命令—RunSQL方法
(9) 关闭打开的对象—Close方法
(10) 退出 Microsoft Access—Quit方法
2
8.1 Docmd对象介绍
(1) 打开窗体—OpenForm方法
Docmd.OpenForm FormName
[,Vew][,FilterName][,WhereCondition][,DataMode]
[,WindowMode]
参数说明：
DataMode：可选项。窗体数据的输入模式
例8-1
DoCmd.OpenForm "学生基本信息", , , , , acDialog
常量
acWindowNormal
acHidden
acIcon
acDialog
值
0
1
2
3
含义
默认值。正常窗口模式
隐藏窗口模式
最小化窗口模式
对话框框模式
3
8.1 Docmd对象介绍
(2) 打开报表—OpenReport方法
命令格式：Docmd.OpenReport ReportName
[,View][,FilterName][,WhereCondition]
参数说明：
ReportName ：字符串表达式，代表当前数据库中报表名称。
View：可选项，打开报表的模式。
例8-2
Docmd.OpenReport "学生信息报表",acViewPreview
常量
acViewNormal
acViewDesign
acViewPreview
值
0
1
2
含义
默认值，打印视图打开
设计视图打开
预览视图打开
4
8.1 Docmd对象介绍
(3) 打开查询—OpenQuery方法
命令格式：Docmd.OpenQuery QueryName [,View]
[,DataMode]
参数说明：
QueryName ：字符串表达式，代表当前数据库中查询
名称。
DataMode：可选项。指定三种输入数据模式：
acAdd(追加)、acEdit（编辑）、acReadOnly（只读）。
例8-3
Docmd.OpenQuery "学生选修成绩",, acReadOnly
5
8.1 Docmd对象介绍
(4) 打开表—OpenTable方法
命令格式：Docmd.OpenTable TableName [,View]
[,DataMode]
参数说明：
TableName：字符串表达式，代表当前数据库中表名
称。
例8-4
DoCmd.OpenTable "学生"
6
8.1 Docmd对象介绍
(5) 打开模块—OpenModule方法
命令格式：Docmd.OpenModule
[ModuleName][,ProcedureName]
参数说明：
ModuleName：字符串表达式，代表当前数据库中模块名
称。若缺省，则在数据库的标准模块中搜索全部由
ModuleName参数指定的过程，并且打开包含这些过程的模
块。
ProcedureName：字符串表达式，代表过程名称。若缺
省，则打开指定模块的通用声明部分。
两个参数至少要设置一个。
例8-5 打开“函数调用”模块的“TryOut”过程。
7
DoCmd.OpenModule "函数调用",
"TryOut"
8.1 Docmd对象介绍
(6) 运行菜单或工具栏命令—RunCommand方法
命令格式：Docmd.RunComand Command
参数说明：
Command：固有常量。指定要执行的内置菜单或工具
栏命令。
例8-6 执行Access的“退出”命令
DoCmd.RunCommand acCmdExit
8
8.1 Docmd对象介绍
(7) 运行宏—RunMacro方法
命令格式：Docmd.RunMacro
MacroName[,RepeatCount][,RepeatExpression]
参数说明：
MacroName: 字符串表达式，代表当前数据库中宏名
称。
RepeatCount：可选项，数值表达式，是一个整型值，
代表宏将要运行的次数。
RepeatExpression：可选项，用来指定重复运行宏的
条件，当条件为False（0）时，停止运行宏。
例8-7 重复运行“宏1”两次。
DoCmd.RunMacro "宏1", 2 9
8.1 Docmd对象介绍
(8) 执行SQL命令—RunSQL方法
命令格式：Docmd.RunSQL
sqlStatement[,Usetransaction]
参数说明：
sqlStatement：字符串表达式，代表操作查询或数据库
定义查询的SQL语句。
Usetransaction：可选项，该参数为True(-1)时，将在事
务处理中包含该查询。否则，不使用事务处理。省缺时，
该值为True。
例8-8 删除“学生”表中性别为男的学生记录。
DoCmd.RunSQL "Delete From 学生 Where 性别='男'"
10
8.1 Docmd对象介绍
(9) 关闭打开的对象—Close方法
命令格式：Docmd .Close [ObjectType][, ObjectName][,
Save]
参数说明：
ObjectType：要关闭的对象类型
ObjectName：字符串表达式，代表要关闭的对象名称。
Save：指定对象关闭时的保存方式。取值为
acSavePrompt(提示保存) 、acSaveYes(保存)。
acSaveNo(不保存)三种。默认值为acSavePrompt。
如果参数都缺省，则关闭当前打开的对象。
例8-9 关闭打开的“学生”表。
DoCmd.Close acTable, "学生"
11
8.1 Docmd对象介绍
(10) 退出 Microsoft Access—Quit方法
命令格式：Docmd.Quit
12
8.2 VBA数据库访问技术
8.2.1 数据库访问技术概述
常用的数据库访问接口技术包括ODBC API、DAO、
ADO等。
ADO简介
ActiveX数据对象（ActiveX Data Object）简称ADO，
是Microsoft公司开发的基于面向对象数据库编程的新
接口。ADO扩展了DAO所使用的对象模型，具有更加
简单、灵活的操作性能。
13
8.2 VBA数据库访问技术
8.2.2 ADO对象模型
详细介绍Connection、Command、Recordset三个主要
对象的使用
(1) Connection对象
①Connection对象的声明
声明的语法格式如下：
Dim对象变量名As ADODB.Connection
其中，“对象变量名”为任意合法的VBA变量名。
例如：Dim cnn As ADODB.Connection
14
8.2 VBA数据库访问技术
8.2.2 ADO对象模型
②Connection对象实例化
在Connection对象声明后，需要实例化Connection对
象后才能使用，例如：
Set cnn=New ADODB.Connection
声明并实例化一个Connection对象，也可写成：
Dim对象变量名As New ADODB.Connection
15
8.2 VBA数据库访问技术
8.2.2 ADO对象模型
③Connection对象的主要属性
Connection对象的主要属性有ConnectionString、
DefaultDatabase、Provider和State。
DefaultDatabase属性：用来指定Connection对象的默
认数据库。
例如，要连接到“学生成绩管理”数据库，可以用如下
代码设置cnn（Connection对象）的DefaultDatabase属
性值。
cnn. DefaultDatabase="学生成绩管理.accdb"
16
8.2 VBA数据库访问技术
 8.2.2 ADO对象模型
④Connection对象的主要方法
Connection对象的主要方法有：Open、Execute和
Close。
说明：以下语法格式中的“对象名”是指已经定义的
Connection对象名。
Open方法：
Open方法用来建立与数据库的连接。语法格式如下：
对象名.Open [ConnectionString] [,UserID][,PassWord][,
Options]
17
8.2 VBA数据库访问技术
8.2.2 ADO对象模型
(2) Recordset对象
当记录集对象（RecordSet）和Connection对象一起使
用时，必须先声明并实例化一个记录集对象。
①RecordSet对象的声明
声明格式如下：
Dim 对象变量名 AS ADODB.Recordset
例如：Dim rs As ADODB.Recordset
18
8.2 VBA数据库访问技术
 8.2.2 ADO对象模型
②RecordSet对象实例化
在RecordSet对象声明后，需实例化RecordSet对象后
才能使用，例如：
Set rs=New ADODB. Recordset
声明并实例化一个Recordset对象也可直接写成：
Dim对象变量名As New ADODB.Recordset
在任何时候，打开的RecordSet对象所指的当前记录均
为整个记录集中的单个记录。
19
8.2 VBA数据库访问技术
③RecordSet对象的主要属性
BOF和EOF属性
当BOF为True时，记录指针在记录集第一条记录前，而EOF
为True时表明记录指针在最后一条记录后。
Filter属性
用于指定记录集的过滤条件，只有满足了条件的记录才会
显示出来，其语法格式为：
记录集对象名.Filter=条件
例8-10 只显示记录集对象rs中部门名称为“财务部”的员工
信息。
Dim rs AS New ADODB.Recordset
rs.Filter='部门名称=财务部'20
8.2 VBA数据库访问技术
③RecordSet对象的主要方法
Open方法
打开一个记录集，需要使用Open方法，其语法格式如
下：
对象名.Open
[Source][,ActiveConnection][,CursorType][,LockType][,
Options]
21
8.2 VBA数据库访问技术
例8-11 在“学生成绩管理”数据库中使用RecordSet对象创建“学生”记
录集，在立即窗口中显示学生表的全部记录。
Sub DemoRecordset()
Dim cnn As ADODB.Connection ' 声明连接对象
Dim rst As ADODB.Recordset
' 声明记录集对象
Set cnn = New ADODB.Connection ' 实例化连接对象
Set rst = New ADODB.Recordset ' 实例化记录集对象
cnn.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0;" & _
"Persist Security Info=False;User ID=Admin;Data Source = F:\学生成绩
管理.accdb "
cnn.Open ' 打开学生成绩管理数据库
rst.Open "SELECT * FROM 学生", cnn ' 用RecordSet对象的Open方法
打开记录集
Debug.Print rst.GetString ' 在立即窗口打印记录集
rst.Close
' 关闭记录集
Set rst = Nothing
' 回收rs对象的内存占有
cnn.Close
' 关闭连接
Set cnn = Nothing
' 回收cnn对象的内存占有
22
End Sub
8.2 VBA数据库访问技术
③RecordSet对象的主要方法
AddNew方法
添加新记录需要使用AddNew方法，其语法格式如下：
对象名.AddNew [FieldList][,Values]
Update方法
更新记录需要使用Update方法，其语法格式如下：
对象名.Update
更新记录后，应使用Update方法将所添加的记录数据存
储在数据库中。
Delete方法
删除记录需要使用Delete方法，其语法格式如下：
23
对象名.Delete [AffectRecords]
8.2 VBA数据库访问技术
④对RecordSet对象字段的引用
对记录集进行操作涉及到记录集中字段的引用。访问
Recordset对象中的字段，可以使用字段编号，字段编
号从0开始。假设Recordset对象rs的第一个字段名为
“学号”，则引用该字段可以使用下列多种方法：
rs("学号")
rs(0)
rs.Fields.Item("学号")
rs.Fields.Item(0)
24
8.2 VBA数据库访问技术
(3) Command对象
①Command对象的声明
Command对象的声明格式如下：
Dim 对象变量名As ADODB.Command
例如：Dim cmd As ADODB.Command
②Command对象的实例化
在Command对象声明后，需实例化Command对象后
才能使用，例如：
Set cmd=New ADODB.Command
声明并实例化一个Recordset对象也可直接写成：
Dim 对象变量名 As New ADODB.Command
25
8.2 VBA数据库访问技术
③Command对象的常用属性
Command对象的常用属性如下：
ActiveConnection属性
CommandText属性
State属性
④Command对象的常用方法Execute方法
对于以记录集返回的Command对象：
Set 记录集对象名
=command.Execute([RecordsAffected][,Parameters][,O
ptions])
对于不以记录集返回的Command对象：
command.Execute
26
[RecordsAffected][,Parameters][
,Options]
8.2 VBA数据库访问技术
例8-12在“学生成绩管理”数据库中，使用Command对象获取“学生”记录集。
Sub DemoCommand()
Dim cnn As ADODB.Connection:Dim rst As ADODB.Recordset:Dim cmd As
ADODB.Command
Set cnn = New ADODB.Connection:Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
cnn.ConnectionString = "Provider= Microsoft.ACE.OLEDB.12.0;" & _
"Persist Security Info=False;User ID=Admin;Data Source = F:\学生成绩管
理.accdb "
cmd.CommandText = "SELECT * FROM 学生"
' 使用SQL语句设置数据源
cmd.ActiveConnection = cnn.ConnectionString
Set rst = cmd.Execute
' 使用Execute方法执行SQL语句，返回记录集
Debug.Print rst.GetString
' 在立即窗口中显示记录集全部记录
rst.Close
Set rst = Nothing:Set cmd = Nothing:Set cnn = Nothing
 End Sub
27
8.2.3 利用ADO访问数据库的步骤
添加对ADO的引用，只需要依次选择VBE窗口的“工具”|“引用”菜
单命令，在弹出的“引用”对话框中选中“Microsoft ActiveX Data
Objects 2.1 Library”选项（有前置的√），按“确定”按钮即可。
在添加了ADO对象的引用后，在VBA中利用ADO访问数据库的基
本步骤如下：
1. 定义和创建ADO对象实例变量
2. 设置连接参数并打开连接
3. 设置命令参数并执行命令
4. 打开记录集，并操作记录集
5. 收尾工作
28
8.2.3 利用ADO访问数据库的步骤
步骤如下：
1. 定义和创建ADO对象实例变量
一般需要创建Connection对象、Recordset对象、Command对
象、Field对象等。
创建Connection对象 例如：Dim cnn As New
ADODB.Connection
创建Recordset对象，例如：Dim rs As New ADODB.Recordset
创建Command对象，例如：Dim cmd As New
ADODB.Command
创建Field对象，例如：Dim fd As ADODB.Field
29
8.2.3 利用ADO访问数据库的步骤
步骤如下：
2. 设置连接参数并打开连接
假定cnn为连接对象，与数据库的连接，可以参照以下代码：
cnn.ConnectionString =连接参数
cnn.Open
如果与当前数据库连接则连接参数为
CurrentProject.Connection
30
8.2.3 利用ADO访问数据库的步骤
步骤如下：
3. 设置命令参数并执行命令
一般需要设置命令对象的活动连接、类型及查询等属性。可以参照
以下代码：
With cmd
.ActiveConnection=连接串
.CommandType=命令类型参数
.CommandText=查询命令串
End With
Cmd.Execute
31
8.2.3 利用ADO访问数据库的步骤
 步骤如下：
4. 打开记录集，并操作记录集
打开记录集，可以在Connection对象上打开记录集，也可以在Command
对象上打开记录集。
在Connection对象打开记录集可以参照以下代码：
cnn.Open ' 打开一个连接
rs.Open 查询串等参数 ' 打开记录集
在Commnd对象上打开记录集，首先要设置命令对象的活动连接、类型及
查询等属性，可以参照以下代码：
rs.Open cmd,其他参数
操作记录集（检索、追加、更新、删除），一般需要使用DO循环来完成，
可参照代码如下：
Do While Not rs.Eof ' 利用循环遍历整个记录集直到末尾
…
' 安排字段数据的各类操作
rs.MoveNext
' 记录指针指向下一条记录
Loop
32
8.2.3 利用ADO访问数据库的步骤
 步骤如下：
5. 收尾工作
在应用程序关闭前，应该关闭并释放分配给ADO对象的资源，
操作系统回收这些资源并分配给其他应用程序。
对象变量名.Close ’关闭对象语法
Set对象变量名=Nothing ’回收资源代码如
通常情况下要关闭并释放对象的有：Connection对象、
RecordSet对象、Command对象等，可参照代码如下：
rs.Close
' 关闭记录集
cnn.Close
' 关闭连接
set rs=Nothing ' 回收rs对象的内存占有
set cmd=Nothing ' 回收cmd对象的内存占有
set cnn=Nothing ' 回收cnn对象的内存占有
33
8.3 综合案例
 案例 编写程序完成用户表的信息查询、添加、更新、删除
等操作。
操作步骤如下：
(1) 在学生成绩管理数据库中建立“用户”表，表结构如图 8-2 所示。
图 8-2 用户表结构
(2) 建立用户信息窗体，窗体界面如 8-3 所示。
34
8.3 综合案例
案例 编写程序完成用户表的信息查询、添加、更新、删除等
操作。
(3) 设置属性，如表 8-12 所示。
表 8-12 控件及主要属性设置
控件
名称
属性及属性值
标签
Label1
标题：用户名
标签
Label2
标题：密码
文本框
Tname
名称：Tname
文本框
Tpass
名称：Tpass
命令按钮
CmdAdd
名称：CmdAdd
标题：添加记录
命令按钮
CmdDel
名称：CmdDel
标题：删除记录
命令按钮
CmdFind
名称：CmdFind
标题：查找记录
命令按钮
CmdUpdate
名称：CmdUpdate 标题：更新记录
35
输入掩码：密码
8.3 综合案例
(4) 编写代码如下：
Private cn As New ADODB.Connection
private rs As New ADODB.Recordset
private strName As ADODB.Field, strPass As ADODB.Field
private strSQL As String
‘定义窗体级变量
36
8.3 综合案例
Private Sub CmdADD_Click() ' 追加记录
Dim cmd As New ADODB.Command ' 创建命令对象cmd
Set rs.ActiveConnection = cn
' 设置记录集对象的当前连接
If IsNull(tuser) And IsNull(tpass) Then ' 用户名、密码同时为空
MsgBox "请输入用户名或密码", vbCritical
Else
rs.Open "Select 用户名 From 用户 Where 用户名='" + tuser + "'" ' 打开记录集
If Not rs.EOF Then ' 找到，说明表中已有该用户，
MsgBox "你输入的用户名已存在，不能新增加！", vbCritical
Else
' 没有找到，新增记录
cmd.ActiveConnection = cn
strSQL = "Insert Into 用户(用户名,密码) Values('" + tuser + "','" + tpass + "')"
cmd.CommandText = strSQL
cmd.Execute ' 执行插入记录操作
MsgBox "添加成功，请继续！"
End If
rs.Close
Set rs = Nothing
End If
End Sub
37
8.3 综合案例
 Private Sub cmddel_Click() ' 删除记录
Set rs.ActiveConnection = cn
strSQL = "Select 用户名 From 用户 Where 用户名='" + tuser + "'" +_
" and 密码='" + tpass + "'"
' 打开用户名与密码与相应文本框值一样的记录集
rs.Open strSQL, , adOpenKeyset, adLockOptimistic
If rs.EOF Then
MsgBox "你输入的用户与密码不存在，不能删除！", vbCritical
Else
rs.Delete
' 删除
rs.Update
' 更新
MsgBox "删除成功"
End If
rs.Close
Set rs = Nothing
38
End Sub
本章小结
 Docmd对象是Access中的重要对象，在VBA编程中通过调
用该对象的内部方法实现对Access数据库的灵活操作，本
章介绍了该对象的调用格式及常用的操作方法。
 常用的数据库访问接口技术包括ODBC API、DAO、ADO
等，本章对数据库访问技术进行了概述，介绍了ADO对象
模型中Connection、Command、Recordset三个重要对
象，归纳出利用ADO访问数据库的基本步骤，最后给出一
个在VBA中访问数据库的简单实例。
39
习题
 一、选择题
1．ADO对象模型层次中可以打开RecordSet对象的是( )。
A．只能是Connection对象
B．只能是Command对象
C．可以是Connection对象和Command对象
D．不存在
2．ADO对象模型包括Connection、Recordset和( )。
A．Database
B．Workspace
C．Command
D．Fiel
3．某窗体中有一个命令按钮，在窗体视图中单击此命令按钮打开一个查
询，需要执行的操作是( )。
A．OpenqQuery
B．OpenForm
C．OpenTable
D．OpenReport
4．在VBA中，实现窗体打开操作的命令是( )。
A．Do.Openform
B．Docmd.OpenForm
C．OpenForm
D．DoOpen.Form
40
习题
 一、选择题
1．ADO对象模型层次中可以打开RecordSet对象的是( )。
A．只能是Connection对象
B．只能是Command
对象
C．可以是Connection对象和Command对象 D．不存在
2．ADO对象模型包括Connection、Recordset和( )。
A．Database
B．Workspace
C．Command
D．Fiel
3．某窗体中有一个命令按钮，在窗体视图中单击此命令按钮打开
一个查询，需要执行的操作是( )。
A．OpenqQuery
B．OpenForm
C．OpenTable
D．OpenReport
4．在VBA中，实现窗体打开操作的命令是( )。
A．Do.Openform
B．Docmd.OpenForm
C．OpenForm
D．DoOpen.Form
41