chap09VBA數(shù)據(jù)庫編程技術

第9章VBA數(shù)據(jù)庫編程技術9.1數(shù)據(jù)訪問接口ADO9.2ADO主要對象9.3VBA在Access中的數(shù)據(jù)庫編程實例第9章本章小結思考與練習本章首先概述數(shù)據(jù)訪問接口，接著將詳細介紹ADO三個主要對象，最后通過實例說明VBA使用ADO對象訪問Access數(shù)據(jù)庫的具體方法與編程技巧。本章的知識結構導航圖如圖9-1所示。9.1數(shù)據(jù)訪問接口ADO9.1.1數(shù)據(jù)庫引擎和接口所謂數(shù)據(jù)庫引擎實際上是一組動態(tài)鏈接庫〔DLL〕，當VBA程序運行時被連接到應用程序從而實現(xiàn)對數(shù)據(jù)庫的數(shù)據(jù)的訪問功能。數(shù)據(jù)庫引擎〔DatabaseEngine〕是應用程序與物理數(shù)據(jù)庫之間的橋梁，是一種通用接口的方式，用戶可以使用統(tǒng)一形式和相同的數(shù)據(jù)訪問與處理方法來訪問各種類型數(shù)據(jù)庫。在VBA中主要提供了3種數(shù)據(jù)庫訪問接口：9.1.1數(shù)據(jù)庫引擎和接口1.ODBCAPI〔OpenDatabaseConnectivityAPI，開放數(shù)據(jù)庫互連應用程序接口〕ODBC為關系數(shù)據(jù)庫編程提供統(tǒng)一的接口，用戶可通過它對不同類型的關系數(shù)據(jù)庫進行操作。但是由于ODBCAPI允許對數(shù)據(jù)庫進行比較接近底層的配置和控制，屬底層數(shù)據(jù)庫接口，在Access應用中，要直接使用ODBCAPI訪問數(shù)據(jù)庫那么需要大量VBA函數(shù)原型聲明和一些繁瑣的、底層的編程。9.1.1數(shù)據(jù)庫引擎和接口2.DAO〔DataAccessObjects，數(shù)據(jù)訪問對象〕DAO既提供了一組基于功能的API函數(shù)，也提供了一個訪問數(shù)據(jù)庫的對象模型，在Access數(shù)據(jù)庫應用程序中，開發(fā)者可利用其中定義的如Database、QueryDef、RecordSet等一系列數(shù)據(jù)訪問對象，實現(xiàn)對數(shù)據(jù)庫的各種操作。9.1.1數(shù)據(jù)庫引擎和接口3.ADO〔ActiveXDataObjects，動態(tài)數(shù)據(jù)對象〕ADO是基于組件的數(shù)據(jù)庫編程接口，它是一個與編程語言無關的COM組件系統(tǒng)，可以對來自多種數(shù)據(jù)提供者的數(shù)據(jù)進行操作。由于ADO是對微軟所支持的數(shù)據(jù)庫進行操作的最有效和最簡單直接的方法。9.1.2ADOADO以OLEDB為根底，對OLEDB底層操作的復雜接口進行封裝，使應用程序通過ADO極其簡單的COM接口，就可以訪問來自OLEDB數(shù)據(jù)源的數(shù)據(jù)，其中不管是關系數(shù)據(jù)庫還是非關系數(shù)據(jù)庫，也不管是本地數(shù)據(jù)庫還是遠程數(shù)據(jù)庫，應用程序、ADO和數(shù)據(jù)源之間關系如圖9-2所示。9.1.2ADO圖9-2應用程序通過ADO訪問不同數(shù)據(jù)源9.1.2ADO與其他數(shù)據(jù)訪問接口相比，ADO具有以下一些優(yōu)點：1.ADO能夠訪問各種支持OLEDB的數(shù)據(jù)源，包括數(shù)據(jù)庫和文本文件、電子表格、電子郵件等數(shù)據(jù)源。2.ADO采用了ActiveX技術，與具體的編程語言無關，任何使用如VC++、Java、VB、Delphi等高級語言編寫的應用程序都可以使用ADO來訪問各類數(shù)據(jù)源。3.ADO將訪問數(shù)據(jù)源的復雜過程抽象成幾個易于理解的具體操作，并由實際對象來完成，因而使用起來簡單方便。9.1.2ADO4.ADO對象模型簡單易用，速度快，資源開銷和網(wǎng)絡流量少，在應用程序和數(shù)據(jù)源之間使用最少的層數(shù)，為應用程序和數(shù)據(jù)源之間提供了輕便、快捷、高性能的接口。5.ADO屬應用層〔高層〕的編程接口，也可以在各種腳本語言〔Script〕中直接使用，特別適合于各種客戶機/效勞器應用系統(tǒng)和基于Web的應用，尤其在腳本語言中訪問Web數(shù)據(jù)庫是ADO的一大優(yōu)勢。9.2ADO主要對象9.2.1ADO對象模型ADO對象模型是對ADO對象集合的完整概括，它能夠更加靈活、有效地發(fā)揮ADO對象的功能特性。ADO對象模型如圖9-3所示，主要包含Connection、Recordset、Command、Parameter、Field、Property和Error共7個對象，ADO對象模型中有關對象、集合的說明見表9-1。Connection、Recordset和Command是ADO對象模型中的三個最核心對象，也是應用程序訪問數(shù)據(jù)源時使用最多的三個對象。9.2.1ADO對象模型圖9-3ADO對象模型9.2.1ADO對象模型9.2.2Connection對象Connection〔連接〕對象用于建立應用程序與指定數(shù)據(jù)源的連接。使用Connection對象實現(xiàn)與指定數(shù)據(jù)源連接的根本步驟為：〔1〕創(chuàng)立Connection對象；〔2〕設置Connection對象的連接字符串ConnectionString屬性，用以指示要連接的數(shù)據(jù)源信息；〔3〕翻開Connection對象，實現(xiàn)應用程序與數(shù)據(jù)源的物理連接；9.2.2Connection對象〔4〕為節(jié)省系統(tǒng)資源，待對數(shù)據(jù)源中數(shù)據(jù)操作結束后，應關閉Connection對象，實現(xiàn)應用程序與數(shù)據(jù)源的物理斷開。要建立應用程序與數(shù)據(jù)源的連接，首先要在應用程序中聲明一個Connection對象，并對其進行初始化，具體方法如下：DimconnAsADODB.Connection

Setconn=NewADODB.Connection創(chuàng)立完Connection對象，在連接數(shù)據(jù)源之前，可利用Connection對象的ConnectionString屬性設置建立連接的數(shù)據(jù)源信息。設置ConnectionString屬性的語法如下：9.2.2Connection對象連接對象變量.ConnectionString="參數(shù)1=參數(shù)1值;參數(shù)2=參數(shù)2值;……“接著就可以用Connection對象的Open方法實現(xiàn)應用程序與數(shù)據(jù)源的物理連接。Open方法的語法如下：連接對象變量.OpenConnectionString,UserID,Password待數(shù)據(jù)源中數(shù)據(jù)操作結束后，應關閉Connection對象，實現(xiàn)應用程序與數(shù)據(jù)源的物理斷開，以節(jié)省系統(tǒng)資源的開銷。關閉Connection對象應使用Close方法，語法如下：連接對象變量.Close9.2.2Connection對象需要說明的是，使用Close方法只是關閉應用程序與數(shù)據(jù)源的物理連接，而Connection對象并未從內存中釋放，要從內存中釋放已有Connection對象應用如下語法：Set連接對象變量=Nothing9.2.3Recordset對象Recordset〔記錄集〕對象用于存儲來自數(shù)據(jù)庫中根本表或命令執(zhí)行結果的記錄全集。Recordset對象中的數(shù)據(jù)在邏輯上由每行的記錄和每列的字段組成，每個字段又表示為一個Field對象。任一時候，Recordset對象所指的當前記錄均為記錄全集中的單個記錄。類似于Connection對象，在使用Recordset對象之前，也應聲明并初始化一個Recordset對象，方法如下：DimrsAsADODB.Recordset

Setrs=NewADODB.Recordset9.2.3Recordset對象創(chuàng)立一Recordset對象之后，就可以通過Recordset對象的Open方法獲取數(shù)據(jù)源操作的記錄集。Open方法語法如下：記錄集對象變量.OpenSource,ActiveConnection,CursorType,LockType,Options9.2.3Recordset對象在數(shù)據(jù)庫應用程序開發(fā)過程中，開發(fā)者可充分利用Recordset對象的屬性或方法實現(xiàn)應用程序對記錄集中的數(shù)據(jù)操作，Recordset對象中常用屬性及方法有。1.ActiveConnection屬性：通過設置ActiveConnection屬性使翻開的數(shù)據(jù)源連接與Connection對象相關聯(lián)，該屬性值為有效的Connection對象變量或包含ConnectionString參數(shù)的連接字符串。2.AbsolutePosition屬性：返回Recordset對象當前記錄的序號位置。3.RecordCount屬性：返回Recordset對象中記錄的數(shù)目。9.2.3Recordset對象4.BOF、EOF屬性：假設當前位置在Recordset對象第一條記錄之前，BOF屬性值為真，否那么為假；假設當前位置在Recordset對象最后一條記錄之后，EOF屬性值為真，否那么為假。5.AddNew方法：該方法將在Recordset對象中添加一條新記錄。語法如下：記錄集對象變量.AddNew6.Delete方法：該方法將刪除Recordset對象中當前記錄，語法如下：記錄集對象變量.Delete9.2.3Recordset對象7.Update方法：該方法將使Recordset對象中當前記錄的更新內容保存到數(shù)據(jù)庫中，語法如下：記錄集對象變量.Update8.Move、MoveFirst、MoveLast、MoveNext、MovePrevious方法：當翻開一個非空Recordset對象時，當前記錄總是定位在第一條記錄，可根據(jù)需要使用這些方法靈活地將記錄指針移動到指定位置，記錄定位語法見表9-6。9.2.3Recordset對象9.2.3Recordset對象9.Close方法：該方法用以關閉一個已翻開的Recordset對象，并且釋放相關的數(shù)據(jù)和為該Recordset對象所申請的資源。語法如下：記錄集對象變量.Close由于使用Close方法只釋放相關的系統(tǒng)資源，Recordset對象并未從內存中釋放，假設還需要翻開該Recordset對象，可直接使用該對象的Open方法。假設要將Recordset對象從內存中完全釋放，應設置Recordset對象為Nothing，語法如下：Set記錄集對象變量=Nothing9.2.4Command對象Command〔命令〕對象用以定義并執(zhí)行針對數(shù)據(jù)源的具體命令，即通過傳遞指定的SQL命令來操作數(shù)據(jù)庫，如建立數(shù)據(jù)表、刪除數(shù)據(jù)表或修改數(shù)據(jù)表結構等；應用程序也可通過Command對象查詢數(shù)據(jù)庫，并將Command對象的運行結果返回給Recordset對象，以便進一步執(zhí)行如增加、刪除、更新、篩選記錄等操作。9.2.4Command對象創(chuàng)立一個Command對象之后，就可利用該對象的屬性及方法對數(shù)據(jù)源提出命令請求，Command對象的常用屬性和方法有：1.ActiveConnection屬性：通過設置ActiveConnection屬性使已翻開的數(shù)據(jù)源連接與Connection對象相關聯(lián)，ActiveConnection屬性值可以是有效的Connection對象變量或包含ConnectionString參數(shù)的連接字符串。2.CommandText屬性：用以表示Command對象要對數(shù)據(jù)源下達的命令，通常設置為能夠完成某個特定功能的SQL語句、數(shù)據(jù)表或存儲過程的調用等。3.Execute方法：這是Command對象的最主要方法，用以執(zhí)行一個由CommandText屬性指定的查詢、SQL語句或存儲過程。9.3VBA在Access中的9.3.1在VBA中引用ADO類庫ADO是采用面向對象方法設計的，ADO各個對象的定義都被集中在ADO類庫中。要使用ADO對象，首先要引用ADO類庫。在VBA中引用ADO類庫的操作步驟如下：1.在VBE環(huán)境中，選擇“工具”菜單下的“引用”命令，翻開“引用”對話框，如圖9-4所示。2.在“可使用的引用”列表框中選中“MicrosoftActiveXDataObjects2.1”復選框。另外，在這個對話框中還可以改變被引用類庫的優(yōu)先級。例如，選中“MicrosoftActiveXDataObjects2.1”這一類庫，單擊“優(yōu)先級”的向上按鈕，可提升ADO類庫的優(yōu)先級。9.3.1在VBA中引用ADO類庫圖9-4“引用”對話框9.3.2VBA數(shù)據(jù)庫編程實例【例9-6】翻開“學生成績管理.mdb”，設計課程成績統(tǒng)計窗體，運行界面如圖9-5所示，要求實現(xiàn)：1.在組合框Combo1中選擇一課程編號，那么對應的課程名稱、學分、任課教師、學時分別顯示在對應文本框中。2.單擊“統(tǒng)計”按鈕，那么在對應文本框中顯示指定課程的所有參考學生人數(shù)、課程平均分、60分以上人數(shù)和不及格人數(shù)。3.假設未指定具體課程編號就單擊“統(tǒng)計”按鈕，那么顯示提示信息。9.3.2VBA數(shù)據(jù)庫編程實例圖9-5課程成績統(tǒng)計窗體9.3.2VBA數(shù)據(jù)庫編程實例設計步驟如下：〔1〕創(chuàng)立窗體并添加相關控件?？丶闹饕獙傩匀绫?-7所示?！?〕利用組合框向導設置Combo1對象的值來源。設置步驟詳見圖9-6。〔3〕對組合框Combo1的Change事件編寫如下代碼：PrivateSubCombo1_Change()

DimrsAsADODB.Recordset

Setrs=NewADODB.Recordset

從"課程"和"教師"數(shù)據(jù)表中獲取指定課程編號的記錄9.3.2VBA數(shù)據(jù)庫編程實例DimSQLstrAsString

SQLstr="Select課程.課程名稱,課程.學時,課程.學分,教師.姓名From課程,教師Where課程.教師編號=教師.教師編號And課程.課程編號="&Me.Combo1&"'"

rs.OpenSQLstr,CurrentProject.Connection,2,2判斷是否獲取指定的記錄IfNotrs.EOF()Then

Me.Text1=rs("課程名稱")

Me.Text2=rs("學分")

Me.Text3=rs("姓名")

Me.Text4=rs("學時")

EndIf

rs.Close

Setrs=Nothing

EndSub9.3.2VBA數(shù)據(jù)庫編程實例〔4〕對“統(tǒng)計”按鈕Command1的Click事件編寫如下代碼：PrivateSubCommand1_Click()

DimrsAsADODB.Recordset

Setrs=NewADODB.Recordset從"成績"數(shù)據(jù)表獲取指定課程編號的所有記錄DimSQLstrAsString

SQLstr="Select*From成績Where課程編號="&Me.Combo1&"'"

rs.OpenSQLstr,CurrentProject.Connection,2,2判斷獲取的記錄集是否為空,非空那么統(tǒng)計各項數(shù)據(jù)9.3.2VBA數(shù)據(jù)庫編程實例IfNotrs.BOF()OrNotrs.EOF()Then

sum=0

n=0

x=0

y=0逐條記錄累加成績,并分別統(tǒng)計及格與不及格人數(shù)DoWhileNotrs.EOF()

sum=sum+rs("成績")

n=n+1

Ifrs("成績")>=60Then

x=x+1

Else

y=y+19.3.2VBA數(shù)據(jù)庫編程實例EndIf

rs.MoveNext

Loop

aver=sum/n

Me.Text5=n

Me.Text6=Int(aver*100+05)/100

Me.Text7=x

Me.Text8=y

Else

未指定課程編號那么提示信息

MsgBox"課程編號為空,請重新指定課程編號“,0+16,"提示"

EndIf

rs.Close

Setrs=Nothing

EndSub【本章小結】本章在介紹Connection、Recordset和Command三個ADO主要對象的具體使用方法根底上，通過列舉有針對性的實例〔如記錄的查詢、添加、更新、刪除、記錄中字段數(shù)據(jù)統(tǒng)計等〕