VBA访问Access数据库

1、第10章 VBA访问Access数据库 前面我们已经学习了Access数据库的各种对象处理数据的方法，实际上，要想快速有效地管理数据，开发出更具有使用价值的Access数据库应用程序，还应当了解和掌握VBA的数据库编程方法。第10章 VBA访问Access数据库10.1 数据库引擎及其接口10.2 ADO10.3 访问记录10.4 在VBA中使用SQL命令10.1 数据库引擎及其接口 VBA是通过Microsoft Jet数据库引擎工具来支持对数据库的访问。 在VBA中主要提供了3种数据库访问接口：(1) ODBC（开放数据库互联应用编程接口）(2) DAO（数据访问对象）(3) ADO（Ac

2、tiveX数据对象）10.2 ADO ADO（ActiveX Data Object）是目前Microsoft通用的数据访问技术。ADO编程模型定义一组对象，用于访问和更新数据源，它提供了一系列方法完成以下任务：连接数据源、查询记录、添加记录、更新记录、删除记录、检查建立连接或执行命令时可能产生的错误。10.2.1 ADO对象ADO是基于组件的数据库访问接口，可以来自多种数据提供者的数据进行读取和写入操作。ADO的3个成员对象：（1）Connection对象（连接对象）：建立应用程序与数据源的连接。（2）Command对象（操作命令对象）：在创建数据连接的基础上，利用Command对象可以实现

3、对数据源的查询、插删除、编辑修改及更新操作。（3）Recordset对象（记录集对象）：执行数据访问或SQL命令得到动态记录集，它被缓存在内存中。ADO的3个集合对象：（1）Errors集合对象。它依赖于 Connection对象的使用。（2）Parameters集合对象。它依赖于Command对象的使用。（3）Fields集合对象。它依赖于Recordset对象的使用。1、Connection对象 该对象用来实现应用程序与数据源的连接。只有连接成功后，Command对象和Recordset对象才能访问某个数据库。2、Command对象 该对象主要作用是在VBA中用SQL语句访问、查询数据库中

4、的数据，可以完成Recordset对象不能完成的操作，如创建表、修改表结构、删除表、将查询结果保存为新表等。3、Recordset对象 Recordset对象的功能最常用、最重要，它可以访问表和查询对象，返回的记录存储在Recordset对象中。通过该对象可以浏览记录、修改记录、添加新记录或者删除特定记录。10.2.2 在Access中引用ADO对象 在Access的模块设计时要想使用ADO对象，首先应该增加一个对ADO库的引用。打开VBE窗口，选择菜单“工具”“引用”命令，弹出“引用”对话框，从“可使用的引用”列表中选择“Microsoft ActiveX Data Objects 2.1

5、Library” 选项。10.2.2 在Access中引用ADO对象 首先在应用程序中声明一个Connection对象，然后创建Recordset对象，编程完成各种数据访问操作。1、声明Connection对象(1) 定义对象Dim cn As ADODB.Connection(2)初始化对象Set cn=CurrentProject.Connection2、声明与打开Recordset对象 在与数据库的连接操作完成后，声明并初始化一个新的Recordset对象，然后打开该对象访问数据。(1)声明并初始化Dim rs As ADODB.RecordsetSet rs=New ADODB.Rec

6、ordset(2)打开一个Recordset对象使用Recordset对象的Open方法可以打开数据表、查询对象或直接引用SQL语句。格式如下：Recordset对象名.Open 表或查询或SQL, Connection对象名, 游标类型,锁类型10.3 访问记录10.3.1 引用记录字段10.3.2 浏览记录10.3.3 编辑数据10.3.1 引用记录字段打开数据表时，默认的当前记录为第一条记录，任何对记录集（表、查询）的访问都是对当前记录进行的。通过程序可以引用每个记录的字段，方法有两种：(1)直接在记录集对象中引用字段名(2)使用记录集对象的Fields(n)属性，n是一个记录中字段从左

7、至右的排序，第一个字段序号为0。10.3.2 浏览记录当VBA程序开打某个记录集时，记录指针自动指向第一条记录。Recordset记录集对象提供了4种方法浏览记录：(1) MoveFirst：指针移到记录集的第一条记录(2) MoveNext ：指针移到记录集当前记录的上一条记录(3) MovePrevious ：指针移到记录集当前记录的下一条记录(4) MoveLast ：指针移到记录集的最后一条记录10.3.3 编辑数据1、AddNew方法添加记录在程序中，使用Recordset对象的AddNew方法添加记录。(1)调用记录集AddNew方法，产生一个空记录；(2)为空记录的各个字段赋值；

8、(3)使用记录集Update方法保存新记录。2、Update方法修改记录在程序中，使用Recordset对象的Update方法实现记录的更新。(1)寻找并将记录指针移到需要修改的记录上；(2)对记录中各个字段的值进行修改；(3)使用Update方法保存所做的修改。3、Delete方法删除记录在程序中要慎重使用Delete方法，因为被删除的记录是无法恢复的。(1) 将记录指针移到需要删除的记录上；(2)使用Delete方法删除当前记录；(3)将某条记录指定为当前记录（一条记录被删除后，Access不能自动使下一条记录成为当前记录）。实例“查询”命令按钮的VBA代码如下。Private Sub 查

9、询_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sql As String Set cn = CurrentProject.Connection If IsNull(Me.xh) Then MsgBox 请输入学号！, vbOKOnly + vbCritical, 提示 Me.xh.SetFocus Exit SubElse sql = select * from 学生基本情况表 where xsh= & Me.xh & rs.Open sql, cn, adOpenDynamic, adLo

10、ckOptimistic, adCmdText If Not rs.EOF Then Me.xm = rs(1) Me.xb = rs(2) Me.csrq = rs(3) Else MsgBox 没有这个学生，请重新输入学号！, vbOKOnly + vbInformation, 提示 Me.xh = End IfEnd If rs.Close cn.Close Set rs = Nothing Set cn = NothingEnd Sub实例单击“修改工资”命令按钮，基本工资增加20%Private Sub 修改工资_Click() Dim cn As New ADODB.Connect

11、ion Dim rs As New ADODB.Recordset Dim fd As ADODB.Field Dim sql As String Set cn = CurrentProject.Connection sql = select jbgz from 教师基本情况表 rs.Open sql, cn, adOpenDynamic, adLockOptimistic, adCmdText Set fd = rs.Fields(jbgz) Do While Not rs.EOF fd = fd * 1.2 rs.Update rs.MoveNext Loop rs.Close cn.Cl

12、ose Set rs = Nothing Set cn = Nothing Form.RefreshEnd Sub10.4 在VBA中使用SQL命令 SQL命令可以简化对数据的访问操作，特别是有些记录集对象不能实现的功能，如创建表、更新表结构、删除表等，都可以用SQL命令来完成。 Access提供了DoCmd对象，该对象的RunSQL方法可以在VBA中用SQL命令对数据源进行操作，具体包括：(1)数据定义：表的创建、修改结构、删除表、生成表(2)数据操作：数据追加、数据更新、数据删除、数据查询(3)建立表间关系（实现实体完整性和参照完整性）(4)索引的建立和删除RunSQL方法的格式如下：DoCmd.RunSQL SQL命令其中SQL命令由一对双引号括起来的。也可以将SQL命令作为字符窜赋给一个字符串变量，RunSQL方法执行该字符串变量，格式如下：Dim 字符串变量 As String字符串变量=SQL命令DoCmd.Ru