10 VBA访问Access数据库

null第10章 VBA访问Access数据库第10章 VBA访问Access数据库 前面我们已经学习了Access数据库的各种对象处理数据的方法，实际上，要想快速有效地管理数据，开发出更具有使用价值的Access数据库应用程序，还应当了解和掌握VBA的数据库编程方法。第10章 VBA访问Access数据库第10章 VBA访问Access数据库10.1 数据库引擎及其接口 10.2 ADO 10.3 访问记录 10.4 在VBA中使用SQL命令10.1 数据库引擎及其接口10.1 数据库引擎及其接口 VBA是通过Microsoft Jet数据库引擎工具来支持对数据库的访问。 在VBA中主要提供了3种数据库访问接口： (1) ODBC（开放数据库互联应用编程接口） (2) DAO（数据访问对象） (3) ADO（ActiveX数据对象）10.2 ADO10.2 ADO ADO（ActiveX Data Object）是目前Microsoft通用的数据访问技术。ADO编程模型定义一组对象，用于访问和更新数据源，它提供了一系列方法完成以下任务： 连接数据源、查询记录、添加记录、更新记录、删除记录、检查建立连接或执行命令时可能产生的错误。10.2.1 ADO对象10.2.1 ADO对象ADO是基于组件的数据库访问接口，可以来自多种数据提供者的数据进行读取和写入操作。 ADO的3个成员对象： （1）Connection对象（连接对象）：建立应用程序与数据源的连接。 （2）Command对象（操作命令对象）：在创建数据连接的基础上，利用Command对象可以实现对数据源的查询、插删除、编辑修改及更新操作。 （3）Recordset对象（记录集对象）：执行数据访问或SQL命令得到动态记录集，它被缓存在内存中。nullADO的3个集合对象： （1）Errors集合对象。它依赖于 Connection对象的使用。 （2）Parameters集合对象。它依赖于Command对象的使用。 （3）Fields集合对象。它依赖于Recordset对象的使用。1、Connection对象1、Connection对象 该对象用来实现应用程序与数据源的连接。只有连接成功后，Command对象和Recordset对象才能访问某个数据库。2、Command对象2、Command对象 该对象主要作用是在VBA中用SQL语句访问、查询数据库中的数据，可以完成Recordset对象不能完成的操作，如创建表、修改表结构、删除表、将查询结果保存为新表等。3、Recordset对象3、Recordset对象 Recordset对象的功能最常用、最重要，它可以访问表和查询对象，返回的记录存储在Recordset对象中。通过该对象可以浏览记录、修改记录、添加新记录或者删除特定记录。10.2.2 在Access中引用ADO对象10.2.2 在Access中引用ADO对象 在Access的模块设计时要想使用ADO对象，首先应该增加一个对ADO库的引用。打开VBE窗口，选择菜单“工具”→“引用”命令，弹出“引用”对话框，从“可使用的引用”列表中选择“Microsoft ActiveX Data Objects 2.1 Library” 选项。10.2.2 在Access中引用ADO对象10.2.2 在Access中引用ADO对象 首先在应用程序中声明一个Connection对象，然后创建Recordset对象，编程完成各种数据访问操作。 1、声明Connection对象 (1) 定义对象 Dim cn As ADODB.Connection (2)初始化对象 Set cn=CurrentProject.Connection2、声明与打开Recordset对象2、声明与打开Recordset对象 在与数据库的连接操作完成后，声明并初始化一个新的Recordset对象，然后打开该对象访问数据。 (1)声明并初始化 Dim rs As ADODB.Recordset Set rs=New ADODB.Recordset null(2)打开一个Recordset对象 使用Recordset对象的Open方法可以打开数据表、查询对象或直接引用SQL语句。格式如下： Recordset对象名.Open 表或查询或SQL, Connection对象名, 游标类型,锁类型10.3 访问记录10.3 访问记录10.3.1 引用记录字段 10.3.2 浏览记录 10.3.3 编辑数据 10.3.1 引用记录字段10.3.1 引用记录字段打开数据表时，默认的当前记录为第一条记录，任何对记录集（表、查询）的访问都是对当前记录进行的。 通过程序可以引用每个记录的字段，方法有两种： (1)直接在记录集对象中引用字段名 (2)使用记录集对象的Fields(n)属性，n是一个记录中字段从左至右的排序，第一个字段序号为0。10.3.2 浏览记录10.3.2 浏览记录当VBA程序开打某个记录集时，记录指针自动指向第一条记录。 Recordset记录集对象提供了4种方法浏览记录： (1) MoveFirst：指针移到记录集的第一条记录 (2) MoveNext ：指针移到记录集当前记录的上一条记录 (3) MovePrevious ：指针移到记录集当前记录的下一条记录 (4) MoveLast ：指针移到记录集的最后一条记录10.3.3 编辑数据10.3.3 编辑数据1、AddNew方法添加记录 在程序中，使用Recordset对象的AddNew方法添加记录。 (1)调用记录集AddNew方法，产生一个空记录； (2)为空记录的各个字段赋值； (3)使用记录集Update方法保存新记录。2、Update方法修改记录2、Update方法修改记录在程序中，使用Recordset对象的Update方法实现记录的更新。 (1)寻找并将记录指针移到需要修改的记录上； (2)对记录中各个字段的值进行修改； (3)使用Update方法保存所做的修改。 3、Delete方法删除记录3、Delete方法删除记录在程序中要慎重使用Delete方法，因为被删除的记录是无法恢复的。 (1) 将记录指针移到需要删除的记录上； (2)使用Delete方法删除当前记录； (3)将某条记录指定为当前记录（一条记录被删除后，Access不能自动使下一条记录成为当前记录）。实例实例null“查询”命令按钮的VBA代码如下。 Private Sub 查询_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sql As String Set cn = CurrentProject.Connection nullIf IsNull(Me.xh) Then MsgBox "请输入学号！", vbOKOnly + vbCritical, "提示" Me.xh.SetFocus Exit Sub Else sql = "select * from 学生基本情况表 where xsh='" & Me.xh & "'" rs.Open sql, cn, adOpenDynamic, adLockOptimistic, 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 If End If null rs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub 实例实例单击“修改工资”命令按钮，基本工资增加20%nullPrivate Sub 修改工资_Click() Dim cn As New ADODB.Connection 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.Close Set rs = Nothing Set cn = Nothing Form.Refresh End Sub10.4 在VBA中使用SQL命令10.4 在VBA中使用SQL命令 SQL命令可以简化对数据的访问操作，特别是有些记录集对象不能实现的功能，如创建表、更新表结构、删除表等，都可以用SQL命令来完成。 Access提供了DoCmd对象，该对象的RunSQL方法可以在VBA中用SQL命令对数据源进行操作，具体包括： null(1)数据定义：表的创建、修改结构、删除表、生成表 (2)数据操作：数据追加、数据更新、数据删除、数据查询 (3)建立表间关系（实现实体完整性和参照完整性） (4)索引的建立和删除nullRunSQL方法的格式如下： DoCmd.RunSQL SQL命令 其中SQL命令由一对双引号括起来的。 也可以将SQL命令作为字符窜赋给一个字符串变量，RunSQL方法执行该字符串变量，格式如下： Dim 字符串变量 As String 字符串变量=SQL命令 DoCmd.RunSQL 字符串变量实例实例建立Create_Table()过程，创建一个名为student的表，包含3个字段：姓名（8字节）、性别（2字节）、出生日期。 Sub Create_Table() Dim sql As String sql = "CREATE TABLE student(姓名 text(8),性别 text(2),出生日期 date)" DoCmd.RunSQL sql End Sub