計算機與信息技術

基於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編輯器中，插入一個用戶窗體，利用控制項工具箱設計如圖１所示布局界面。

圖12.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-），男，安徽懷遠人，軟體測試工程師，大學本科，研究方向為軟體開發。
推薦閱讀：