VBA訪問Access數(shù)據(jù)庫

1、第10章 VBA訪問Access數(shù)據(jù)庫 前面我們已經(jīng)學習了Access數(shù)據(jù)庫的各種對象處理數(shù)據(jù)的方法，實際上，要想快速有效地管理數(shù)據(jù)，開發(fā)出更具有使用價值的Access數(shù)據(jù)庫應用程序，還應當了解和掌握VBA的數(shù)據(jù)庫編程方法。第10章 VBA訪問Access數(shù)據(jù)庫10.1 數(shù)據(jù)庫引擎及其接口10.2 ADO10.3 訪問記錄10.4 在VBA中使用SQL命令10.1 數(shù)據(jù)庫引擎及其接口 VBA是通過Microsoft Jet數(shù)據(jù)庫引擎工具來支持對數(shù)據(jù)庫的訪問。 在VBA中主要提供了3種數(shù)據(jù)庫訪問接口：(1) ODBC（開放數(shù)據(jù)庫互聯(lián)應用編程接口）(2) DAO（數(shù)據(jù)訪問對象）(3) ADO（Ac

2、tiveX數(shù)據(jù)對象）10.2 ADO ADO（ActiveX Data Object）是目前Microsoft通用的數(shù)據(jù)訪問技術。ADO編程模型定義一組對象，用于訪問和更新數(shù)據(jù)源，它提供了一系列方法完成以下任務：連接數(shù)據(jù)源、查詢記錄、添加記錄、更新記錄、刪除記錄、檢查建立連接或執(zhí)行命令時可能產(chǎn)生的錯誤。10.2.1 ADO對象ADO是基于組件的數(shù)據(jù)庫訪問接口，可以來自多種數(shù)據(jù)提供者的數(shù)據(jù)進行讀取和寫入操作。ADO的3個成員對象：（1）Connection對象（連接對象）：建立應用程序與數(shù)據(jù)源的連接。（2）Command對象（操作命令對象）：在創(chuàng)建數(shù)據(jù)連接的基礎上，利用Command對象可以實現(xiàn)

3、對數(shù)據(jù)源的查詢、插刪除、編輯修改及更新操作。（3）Recordset對象（記錄集對象）：執(zhí)行數(shù)據(jù)訪問或SQL命令得到動態(tài)記錄集，它被緩存在內(nèi)存中。ADO的3個集合對象：（1）Errors集合對象。它依賴于 Connection對象的使用。（2）Parameters集合對象。它依賴于Command對象的使用。（3）Fields集合對象。它依賴于Recordset對象的使用。1、Connection對象 該對象用來實現(xiàn)應用程序與數(shù)據(jù)源的連接。只有連接成功后，Command對象和Recordset對象才能訪問某個數(shù)據(jù)庫。2、Command對象 該對象主要作用是在VBA中用SQL語句訪問、查詢數(shù)據(jù)庫中

4、的數(shù)據(jù)，可以完成Recordset對象不能完成的操作，如創(chuàng)建表、修改表結構、刪除表、將查詢結果保存為新表等。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對象，然后創(chuàng)建Recordset對象，編程完成各種數(shù)據(jù)訪問操作。1、聲明Connection對象(1) 定義對象Dim cn As ADODB.Connection(2)初始化對象Set cn=CurrentProject.Connection2、聲明與打開Recordset對象 在與數(shù)據(jù)庫的連接操作完成后，聲明并初始化一個新的Recordset對象，然后打開該對象訪問數(shù)據(jù)。(1)聲明并初始化Dim rs As ADODB.RecordsetSet rs=New ADODB.Rec

6、ordset(2)打開一個Recordset對象使用Recordset對象的Open方法可以打開數(shù)據(jù)表、查詢對象或直接引用SQL語句。格式如下：Recordset對象名.Open 表或查詢或SQL, Connection對象名, 游標類型,鎖類型10.3 訪問記錄10.3.1 引用記錄字段10.3.2 瀏覽記錄10.3.3 編輯數(shù)據(jù)10.3.1 引用記錄字段打開數(shù)據(jù)表時，默認的當前記錄為第一條記錄，任何對記錄集（表、查詢）的訪問都是對當前記錄進行的。通過程序可以引用每個記錄的字段，方法有兩種：(1)直接在記錄集對象中引用字段名(2)使用記錄集對象的Fields(n)屬性，n是一個記錄中字段從左

7、至右的排序，第一個字段序號為0。10.3.2 瀏覽記錄當VBA程序開打某個記錄集時，記錄指針自動指向第一條記錄。Recordset記錄集對象提供了4種方法瀏覽記錄：(1) MoveFirst：指針移到記錄集的第一條記錄(2) MoveNext ：指針移到記錄集當前記錄的上一條記錄(3) MovePrevious ：指針移到記錄集當前記錄的下一條記錄(4) MoveLast ：指針移到記錄集的最后一條記錄10.3.3 編輯數(shù)據(jù)1、AddNew方法添加記錄在程序中，使用Recordset對象的AddNew方法添加記錄。(1)調(diào)用記錄集AddNew方法，產(chǎn)生一個空記錄；(2)為空記錄的各個字段賦值；

8、(3)使用記錄集Update方法保存新記錄。2、Update方法修改記錄在程序中，使用Recordset對象的Update方法實現(xiàn)記錄的更新。(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命令可以簡化對數(shù)據(jù)的訪問操作，特別是有些記錄集對象不能實現(xiàn)的功能，如創(chuàng)建表、更新表結構、刪除表等，都可以用SQL命令來完成。 Access提供了DoCmd對象，該對象的RunSQL方法可以在VBA中用SQL命令對數(shù)據(jù)源進行操作，具體包括：(1)數(shù)據(jù)定義：表的創(chuàng)建、修改結構、刪除表、生成表(2)數(shù)據(jù)操作：數(shù)據(jù)追加、數(shù)據(jù)更新、數(shù)據(jù)刪除、數(shù)據(jù)查詢(3)建立表間關系（實現(xiàn)實體完整性和參照完整性）(4)索引的建立和刪除RunSQL方法的格式如下：DoCmd.RunSQL SQL命令其中SQL命令由一對雙引號括起來的。也可以將SQL命令作為字符竄賦給一個字符串變量，RunSQL方法執(zhí)行該字符串變量，格式如下：Dim 字符串變量 As String字符串變量=SQL命令DoCmd.Ru