基于Excel VBA的数据库访问技术的探究

李娜¹  武春杰²（1. 海军蚌埠士官学校 计算机教研室，安徽 蚌埠 233012；2.富士康科技集团，山东 烟台 264000）     摘  要　采用Excel自带的VBA编程环境设计程序代码，利用ADO技术访问Access数据库，实现了数据的查询、添加、删除、更新等操作。    关键词　VBA；ADO；Access；数据库 

1　引言

    尽管每个工作簿可以存放很多数据，但是，如果将工作簿的每张工作表都保存满数据，一方面将占用大量的空间，另一方面也使得工作表的计算速度变得极为缓慢，因此，当要保存大量的数据时，应当使用数据库。

1.1　VBA

    VBA (Visual Basic for Application)是用于开发应用程序的Basic语言。它是Visual Basic的子集，但脱离于VB的编程环境。VBA不是独立存在的语言，必须基于一个主应用程序，例如基于Microsoft Excel的VBA。VBA代码和Excel文件是保存在一起的，可以通过Visual Basic编辑器打开VBA的IDE环境进行程序设计，同时VBA又具有丰富的控件、完备的语言系统和ActiveX　Automation技术，从而可实现一个复杂的信息管理系统。

1.2　ADO

    ADO(ActiveX Data Objects)又称为OLE 自动化接口，是Microsoft提供的一种面向对象的数据访问应用编程接口，是目前Microsoft通用的数据库访问技术。如果需要在VBA代码中操作数据库，或者需要比较灵活地读入数据库的数据，那么就需要使用ADO技术来导入数据，其主要优点是易于使用，速度快，内存支出少和磁盘遗迹小。    ADO对象模型定义了一组对象，用于访问和更新数据源，它提供了一系列的方法完成各种任务。其核心对象是Connection对象、Recordset对象、Command对象，其中Connection对象是ADO对象中最高级的对象，它用于建立与数据源的连接；Command对象可以在数据源中查询、添加、删除和更新数据；Recordset对象只代表记录集，是基于某个连接的表或Command对象执行结果，在ADO对象模型，所有对数据源的操作几乎都是Recordset对象完成的。

2　实现过程

    打开Excel文件，进入Excel VBA环境进行主体程序设计。为了创建数据库与Excel的连接，首先必须设置引用，即必须设置ADO控件。几个需要引用的项目如下：    Microsoft ADO Ext2.8 for DDL and Security    Microsoft Active Data Objects(Multi-dimensional) 2.8 Library    Microsoft Active Data Objects Recordset 2.8 Library    Microsoft Active Data Objects 2.8 Library    Microsoft Jet and Replication  Objects 2.6 Library    引用的方法是：在Excel VBA编辑器窗口中，执行“工具”引用，打开“引用VBAProject”对话框，选择相应的项目即可。    本例以“教职工信息管理系统”说明如何对数据库进行查询、添加、删除、更新等操作。

2.1　窗体设计

    在VBA编辑器中，插入一个用户窗体，利用控件工具箱设计如图１所示布局界面。图1

2.2　程序设计

    其中部分代码如下：    Public cnn As New ADODB.Connection    Public rs As New ADODB.Recordset    ‘窗体加载事件代码    Private Sub UserForm1_load()     Dim mydata As String, mytable As StringWith Cmbxb    .AddItem "男"    .AddItem "女"End WithWith Cmbbm    .AddItem "计算机教研室"    .AddItem "数学教研室"    .AddItem "语言教研室"    .AddItem "物理教研室"    .AddItem "美术教研室"    .AddItem "体育教研室"    .AddItem "化学教研室"    .AddItem "生物教研室"End WithWith Cmbzw    .AddItem "教授"    .AddItem "副教授"    .AddItem "讲师"    .AddItem "助教"End WithWith Cmbzc    .AddItem "初级"    .AddItem "中级"    .AddItem "高级"End WithDTPcsrq.Value = DataDTPcjgzsj.Value = Datamydata = "F:\论文\基于Excel VBA的数据库访问技术的探究\教职工管理.mdb"mytable = "教职工资料表"Set cnn = New ADODB.Connectioncnn.Provider = "Microsoft.Jet.OLEDB.4.0"cnn.Open mydataSet rs = New ADODB.Recordsetrs.Open mytable, cnn, adOpenKeyset, adLockOptimisticCall 显示信息End Sub‘显示信息过程Public Sub 显示信息()If rs.BOF = True And rs.EOF = True Then    Call 清除Else    Textbh.Value = rs.Fields(0)    Textxm.Value = rs.Fields(1)    Cmbxb.Value = rs.Fields(2)    Cmbbm.Value = rs.Fields(3)    Cmbzw.Value = rs.Fields(4)    Cmbzc.Value = rs.Fields(5)    DTPcjgzsj.Value = rs.Fields(6)    DTPcsrq.Value = rs.Fields(7)    Textgzze.Value = rs.Fields(8)End IfEnd Sub‘清除信息过程Public Sub 清除()    Textbh.Value = ""    Textxm.Value = ""    Cmbxb.Value = ""    Cmbbm.Value = ""    Cmbzw.Value = ""    Cmbzc.Value = ""    DTPcjgzsj.Value = ""    DTPcsrq.Value = ""    Textgzze.Value = ""    DTPcjgzsj.Value = ""    DTPcsrq.Value = ""End Sub查看记录中的第一条、下一条、上一条、最后一条，代码类似，以第一条为例，代码如下：Private Sub Cmdbfir_Click() '第一条记录If rs.BOF = True And rs.EOF = True Then    Exit SubElse    rs.MoveFirst    Call 显示信息End IfEnd Sub查询记录、添加记录、删除记录、更新记录等操作代码类似，以查询为例，代码如下：Private Sub Cmdbque_Click()begin:    Call 清除    Textxm = InputBox("请输入要查询的职工姓名", "查询")    rs.MoveFirst    Do While Not rs.EOF '如果不是最后一个记录之后执行下列语句    If rs.Fields(1) = Textxm.Value Then        Call 显示信息        Exit Sub    Else        rs.MoveNext    End If    Loop    MsgBox "没有查找到姓名为""&Textxm.Value&""的职工记录!", vbInformation + vbOKOnly, "查询记录"    GoTo begin    Call 清除End Sub    运行窗体，就可以对教职工数据进行查看、查询、添加、删除、更新等操作，运行后的界面如图2所示。图2
    由于VBA代码与Excel文件是绑定在一起的，可以直接运行Excel文件，宏代码即自动运行。

3　结语

    通过在Excel VBA环境下编写程序代码，可以成功地访问数据库，只要打开Excel文件就可自动运行，操作十分方便。

参考文献

    [1]韩小良．Excel VBA行政与人力资源管理应用案例详解[M]．北京：中国铁道出版社，2006.10    [2](美)拉美尔(Rahmel,D.)著，刘成勇译.Visual Basic 6数据库编程24学时教程[M]．北京:机械工业出版社，1999    [3]李文瑞．VBA/Excel 2000让我把工作变轻松了[M]．北京：人民邮电出版社，2000    收稿日期：4月13日   修改日期：6月2日   作者简介：李娜（1983-），女，安徽砀山人，助教，大学本科，研究方向为计算机应用基础；武春杰（1981-），男，安徽怀远人，软件测试工程师，大学本科，研究方向为软件开发。