1、第10章 VBA訪問Access數(shù)據(jù)庫 前面我們已經(jīng)學(xué)習(xí)了Access數(shù)據(jù)庫的各種對(duì)象處理數(shù)據(jù)的方法，實(shí)際上，要想快速有效地管理數(shù)據(jù)，開發(fā)出更具有使用價(jià)值的Access數(shù)據(jù)庫應(yīng)用程序，還應(yīng)當(dāng)了解和掌握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ù)庫引擎工具來支持對(duì)數(shù)據(jù)庫的訪問。 在VBA中主要提供了3種數(shù)據(jù)庫訪問接口：(1) ODBC（開放數(shù)據(jù)庫互聯(lián)應(yīng)用編程接口）(2) DAO（數(shù)據(jù)訪問對(duì)象）(3) ADO（Ac

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

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

4、的數(shù)據(jù)，可以完成Recordset對(duì)象不能完成的操作，如創(chuàng)建表、修改表結(jié)構(gòu)、刪除表、將查詢結(jié)果保存為新表等。3、Recordset對(duì)象 Recordset對(duì)象的功能最常用、最重要，它可以訪問表和查詢對(duì)象，返回的記錄存儲(chǔ)在Recordset對(duì)象中。通過該對(duì)象可以瀏覽記錄、修改記錄、添加新記錄或者刪除特定記錄。10.2.2 在Access中引用ADO對(duì)象 在Access的模塊設(shè)計(jì)時(shí)要想使用ADO對(duì)象，首先應(yīng)該增加一個(gè)對(duì)ADO庫的引用。打開VBE窗口，選擇菜單“工具”“引用”命令，彈出“引用”對(duì)話框，從“可使用的引用”列表中選擇“Microsoft ActiveX Data Objects 2.1

5、Library” 選項(xiàng)。10.2.2 在Access中引用ADO對(duì)象 首先在應(yīng)用程序中聲明一個(gè)Connection對(duì)象，然后創(chuàng)建Recordset對(duì)象，編程完成各種數(shù)據(jù)訪問操作。1、聲明Connection對(duì)象(1) 定義對(duì)象Dim cn As ADODB.Connection(2)初始化對(duì)象Set cn=CurrentProject.Connection2、聲明與打開Recordset對(duì)象 在與數(shù)據(jù)庫的連接操作完成后，聲明并初始化一個(gè)新的Recordset對(duì)象，然后打開該對(duì)象訪問數(shù)據(jù)。(1)聲明并初始化Dim rs As ADODB.RecordsetSet rs=New ADODB.Rec

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

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

8、(3)使用記錄集Update方法保存新記錄。2、Update方法修改記錄在程序中，使用Recordset對(duì)象的Update方法實(shí)現(xiàn)記錄的更新。(1)尋找并將記錄指針移到需要修改的記錄上；(2)對(duì)記錄中各個(gè)字段的值進(jìn)行修改；(3)使用Update方法保存所做的修改。3、Delete方法刪除記錄在程序中要慎重使用Delete方法，因?yàn)楸粍h除的記錄是無法恢復(fù)的。(1) 將記錄指針移到需要?jiǎng)h除的記錄上；(2)使用Delete方法刪除當(dāng)前記錄；(3)將某條記錄指定為當(dāng)前記錄（一條記錄被刪除后，Access不能自動(dòng)使下一條記錄成為當(dāng)前記錄）。實(shí)例“查詢”命令按鈕的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 請(qǐng)輸入學(xué)號(hào)！, vbOKOnly + vbCritical, 提示 Me.xh.SetFocus Exit SubElse sql = select * from 學(xué)生基本情況表 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 沒有這個(gè)學(xué)生，請(qǐng)重新輸入學(xué)號(hào)！, vbOKOnly + vbInformation, 提示 Me.xh = End IfEnd If rs.Close cn.Close Set rs = Nothing Set cn = NothingEnd Sub實(shí)例單擊“修改工資”命令按鈕，基本工資增加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命令可以簡化對(duì)數(shù)據(jù)的訪問操作，特別是有些記錄集對(duì)象不能實(shí)現(xiàn)的功能，如創(chuàng)建表、更新表結(jié)構(gòu)、刪除表等，都可以用SQL命令來完成。 Access提供了DoCmd對(duì)象，該對(duì)象的RunSQL方法可以在VBA中用SQL命令對(duì)數(shù)據(jù)源進(jìn)行操作，具體包括：(1)數(shù)據(jù)定義：表的創(chuàng)建、修改結(jié)構(gòu)、刪除表、生成表(2)數(shù)據(jù)操作：數(shù)據(jù)追加、數(shù)據(jù)更新、數(shù)據(jù)刪除、數(shù)據(jù)查詢(3)建立表間關(guān)系（實(shí)現(xiàn)實(shí)體完整性和參照完整性）(4)索引的建立和刪除RunSQL方法的格式如下：DoCmd.RunSQL SQL命令其中SQL命令由一對(duì)雙引號(hào)括起來的。也可以將SQL命令作為字符竄賦給一個(gè)字符串變量，RunSQL方法執(zhí)行該字符串變量，格式如下：Dim 字符串變量 As String字符串變量=SQL命令DoCmd.Ru