第8章

VBA數據庫編程主編：潘惠勇

夏敏捷Access數據庫技術及應用1教學目標了解數據庫引擎的作用。了解Access2016訪問數據庫的幾種接口技術。了解DAO數據庫編程方法。掌握ADO數據庫編程的方法。2第8章-VBA數據庫編程第8章VBA數據庫編程8.1VBA數據庫編程相關基礎知識8.2

數據訪問對象8.3ActiveX數據對象8.4特殊域聚合函數和RunSQL方法8.5綜合案例3第8章-VBA數據庫編程8.1VBA數據庫編程相關基礎知識8.1.1VBA數據庫應用程序一般框架8.1.2VBA數據庫訪問接口8.1.3VBA訪問的數據類型4第8章-VBA數據庫編程8.1.1VBA數據庫應用程序一般框架它由3部分組成:

應用程序界面是用戶和應用程序的接口，用來輸入和展現數據信息；

數據庫負責數據的存儲；

數據庫引擎實際上是一組動態鏈接庫（DLL），當程序運行時被連接到VBA程序而實現對數據庫數據的訪問功能5第8章-VBA數據庫編程8.1.2VBA數據庫訪問接口（1）開放數據庫互連應用編程接口（OpenDatabaseConnectivityAPI，ODBCAPI）（2）數據訪問對象（DataAccessObject，DAO）（3）ActiveX數據對象（ActiveXDataObjects，ADO）6第8章-VBA數據庫編程8.1.3VBA訪問的數據類型VBA訪問的數據庫有3種：（1）本地數據庫本地數據庫文件格式與MicrosoftAccess相同。Jet引擎直接創建和操作這些數據庫。（2）外部數據庫

訪問符合“索引順序訪問文件方法（ISAM）”數據庫，包括dBaseIII、dBaseIV、Foxpro2.0和2.5以及Paradox3.x和4.x。（3）ODBC數據庫

訪問符合ODBC標準的客戶機/服務器數據庫，如MicrosoftSQLServer。7第8章-VBA數據庫編程8.2數據訪問對象8.2.1DAO庫的引用方法8.2.2DAO模型層次結構8.2.3利用DAO訪問數據庫的一般步驟8第8章-VBA數據庫編程8.2.1DAO庫的引用方法

將“MicrosoftOffice16.0AccessdatabaseengineObjectLibrary”選項選中，在Windows10環境下，默認該項引用是選中的。9第8章-VBA數據庫編程8.2.2DAO模型層次結構10第8章-VBA數據庫編程①DBEngine對象：表示數據庫引擎，包含并控制模型中的其他對象。②WorkSpace對象：表示工作區。③DataBase對象：表示操作的數據庫對象。④RecordSet對象：表示數據操作返回的記錄集，可以來自于表、查詢或SQL語句的運行結果。⑤Field對象：代表在數據集中的某一列。⑥QueryDef對象：表示數據庫查詢信息。⑦Error對象：包含使用DAO對象產生的錯誤信息。8.2.3利用DAO訪問數據庫的一般步驟（1）首先定義各個對象變量DimwsAsDAO.Workspace '定義Workspace對象DimdbAsDAO.Database '定義數據庫對象DimrsAsDAO.Recordset '定義記錄集對象（2）為各個對象賦值Setws=DBEngine.Workspaces(0) '將默認工作空間賦值給wsSetdb=ws.OpenDatabase("需要打開的數據庫") '打開指定的數據庫Setrs=db.OpenRecordset(<表、查詢、SQL語句>) '打開指定記錄集并賦值給rs11第8章-VBA數據庫編程3）一般利用循環操作記錄集DoWhileNotrs.EOF...rs.MoveNextLoop（4）關閉并回收對象所占內存rs.Close '關閉記錄集db．Close '關閉數據庫Setrs=Nothing '釋放rs對象內存空間Setdb=Nothing '釋放db對象內存空間12第8章-VBA數據庫編程編輯、添加、刪除、移動記錄操作13第8章-VBA數據庫編程【例8.1】利用DAO對象操作“學籍管理”數據庫，將“學生”表中非漢族學生的民族修改為少數民族14第8章-VBA數據庫編程分析：利用DAO對象打開“學生”表，從第一條記錄開始判斷該學生是否為漢族，如果不是，則將其“民族”設置為“少數民族”，然后將記錄指針向下移動一條記錄，繼續判斷，直至記錄指針移動到記錄集的末尾PublicSubDAOTest()DimwsAsDAO.Workspace '定義Workspace對象DimdbAsDAO.Database '定義數據庫對象DimrsAsDAO.Recordset '定義記錄集對象Setws=DBEngine.Workspaces(0) '將默認工作空間賦值給ws'打開制定的數據庫Setdb=ws.OpenDatabase("F:\寫書實例\學籍管理.accdb")Setrs=dB．OpenRecordset("學生") '打開學生表，并將記錄集賦值給rs'循環操作記錄集DoWhileNotrs.EOFIfrs.Fields("mz")<>"漢族"Thenrs.Editrs.Fields("mz")="少數民族"rs.UpdateEndIfrs.MoveNextLooprs.Close '關閉記錄集dB．Close '關閉數據庫Setrs=Nothing '釋放rs對象內存空間Setdb=Nothing '釋放db對象內存空間EndSub15第8章-VBA數據庫編程8.3ActiveX數據對象8.3.1ADO庫的引用方法8.3.2ADO模型層次結構8.3.3主要ADO對象的使用16第8章-VBA數據庫編程8.3.1ADO庫的引用方法其引用方法和DAO庫的操作方法相同，當出現如圖8.2所示的界面時，選中列表框中“MicrosoftActiveDataObjects2.1Library”前面的選項17第8章-VBA數據庫編程8.3.2ADO模型層次結構18第8章-VBA數據庫編程（1）Connection對象用來建立與數據庫的連接，相當于在應用程序和數據庫中建立一條數據傳輸線。（2）Command對象用來對數據庫執行命令，如查詢、添加、刪除、修改記錄。（3）RecordSet對象用來得到從數據庫返回的記錄集。這個記錄集是一個連接的數據庫中的表，或者是Command對象執行結果返回的記錄集。所有對數據的操作幾乎都是在RecordSet對象中完成的，可以完成指定行、移動行、添加、刪除和修改記錄。（4）Field對象表示記錄集中的字段數據信息。（5）Error對象表示程序出錯時的擴展信息。8.3.3主要ADO對象的使用1．Connection對象定義一個Connection對象的方法如下：DimcnnAsADODB.ConnectionConnection對象必須實例化后才可以使用，實例化方法如下：Setcnn=NewADODB.Connection19第8章-VBA數據庫編程ConnectionString屬性該屬性指示用于建立到數據源的連接信息。常見的設置方法如下。

設置數據庫提供者的名稱：cnn.Provider="Microsoft.ACE.OLEDB.16.0"'Access2016數據庫提供者名稱

指定連接的數據庫名稱：cnn.ConnectionString="DataSource=F:\數據庫實例\學籍管理.accdb"也可以通過DefaultDatabase屬性指定Connection對象的默認數據庫，如要連接“學籍管理”數據庫，可以設置DefaultDatabase的屬性值為：cnn.DefaultDatabase="F:\數據庫實例\學籍管理.accdb"20第8章-VBA數據庫編程Open方法可以調用open方法打開連接，方法如下：cnn.open如果Connection對象沒有設置相關屬性，也可以利用帶參數選項的open方法打開連接，open方法的語法格式為：連接對象名.OpenConnectionString,UserId,Password,Options例如：cnn.Open"Provider=Microsoft.ACE.OLEDB.12.0;DataSource=F:\數據庫實例\學籍管理.accdb"21第8章-VBA數據庫編程Close方法當連接不使用時，一定要斷開連接，比如斷開cnn對象的連接，其方法為：cnn.close22第8章-VBA數據庫編程【例8.2】利用ADO編程連接“學籍管理”數據庫實例。在標準模塊下建立一個過程MyConnection，代碼如下：PublicSubMyConnection()DimcnnAsADODB.Connection'聲明Connection對象Setcnn=NewADODB.Connection'實例化該對象'flag_begincnn.Provider="Microsoft.ACE.OLEDB.16.0"'Access2016數據庫提供者的名稱'指定連接的數據庫名稱cnn.ConnectionString="DataSource=F:\數據庫實例\學籍管理.accdb"'flag_endcnn.Open'打開連接cnn.Close'關閉連接Setcnn=Nothing'撤銷連接EndSub活動連接CurrentProject.Connection，即將注釋flag_begin和flag_end之間的語句修改為：cnn.OpenCurrentProject.Connection23第8章-VBA數據庫編程2．Command對象ADO的Command對象用來執行對數據源的請求，獲得數據集。Command對象也需要先定義，方法如下：DimcmdAsADODB.Command'聲明Command對象Setcmd=NewADODB.Command'實例化該對象Command對象常用屬性有ActiveConnection和CommandText屬性。ActiveConnection屬性用來指明Command對象所要關聯的連接對象；CommandText屬性指明查詢命令的文本內容，可以是一個表，也可以是SQL語句。設置完Command對象的ActiveConnection和CommandText屬性后，最后調用Command的Execute方法返回所需要的記錄集。Execute方法的語法結構如下。Setrecordset=cmd.Execute(RecordsAffected,Parameters,Options)這三個參數可以省略24第8章-VBA數據庫編程【例8.3】利用ADO編程，將學生高考成績統一加10分。25第8章-VBA數據庫編程分析：可以將Command對象的CommandText屬性設置為SQL語句，建立當前數據庫的連接，然后執行Command對象的無返回記錄集的Execute方法，代碼如下。PublicSubMyCommand()DimcmdAsADODB.CommandSetcmd=NewADODB.Command'將當前連接作為cmd對象的活動連接cmd．ActiveConnection=CurrentProject.Connection'將CommandText設置為SQL語句cmd.CommandText="update學生setgkcj=gkcj+10"'執行不返回記錄集的操作cmd.ExecuteEndSub運行該過程，然后打開“學生”表，會發現高考成績已經增加了10分。3．Recordset對象RecordSet對象是最常用的一個ADO對象，從后臺數據庫中查詢所需要的記錄就存放在記錄集中。記錄集由行和列組成，像二維表一樣，利用RecordSet對象的相關屬性和方法可以對記錄集中的數據進行查詢、增加、修改、刪除等操作。RecordSet對象的定義如下。

DimrstAsADODB.Recordset

Setrst=NewADODB.Recordset定義完記錄集對象rst后，下面就要為對象獲取所要的記錄集。獲取的方法很多，下面介紹幾種常用的方法。26第8章-VBA數據庫編程3．Recordset對象（1）通過Connection對象的Execute方法獲得語法格式為：Setrecordset=Connection.Execute（CommandText,RecordsAffected,Options)參數說明如下。CommandText：字符串值，包含要執行的SQL語句、存儲過程、URL或提供程序特定文本，還可以使用表名稱。RecordsAffected：可選，一個Long型變量，提供程序向其返回操作所影響的記錄數。Options：可選，長整型值，用于指示提供程序評估CommandText參數的方式。PublicSubrstByConnection()DimcnnAsNewADODB.Connection '聲明的時候直接實例化對象Setcnn=CurrentProject.ConnectionDimrstAsNewADODB.Recordset '聲明的時候直接實例化對象Setrst=cnn.Execute("select*from教師") '返回記錄集Debug.Printrst.Fields(0),rst.Fields(1),rst.Fields(2)cnn.CloseEndSub該例獲取教師表中的所有數據，存儲到記錄集rst對象中，游標當前處于第一條記錄，然后在立即窗口打印出該條記錄的前3個字段的信息。這里Fields是RecordSet對象下的對象集合，包括了它的所有對象，通過Fields對象可以訪問記錄集中的各個字段，Fields(0)代表第一個字段，fields(1)代表第二個字段，依此類推。27第8章-VBA數據庫編程3．Recordset對象（2）通過Command對象的Execute方法獲得例8.3中執行的是Command對象的不返回記錄集的Execute方法，實際編程中通過Command對象的Execute方法返回記錄集是獲得記錄集最常用的方法。PublicSubrstByCommand()DimcnnAsNewADODB.Connection'聲明的時候直接實例化對象DimcmdAsNewADODB.CommandDimrstAsNewADODB.Recordsetcnn.Provider="Microsoft.ACE.OLEDB.16.0"cnn.ConnectionString="F:\數據庫實例\學籍管理.accdb"cnn.Opencmd.ActiveConnection=cnncmd.CommandText="select*from教師"Setrst=cmd.Execute'返回記錄集Debug.Printrst("gh"),rst("xm"),rst("xb")cnn.CloseEndSub這里通過rst("字段名")方法也可以獲得記錄集中各個字段的值。28第8章-VBA數據庫編程3．Recordset對象（3）通過Recordset對象的Open方法獲得語法格式為：recordset.OpenSource,ActiveConnection,CursorType,LockType,Options參數說明如下。Source：可選，變量型，取值為有效的Command對象、SQL語句、表名稱等。ActiveConnection：可選，取值為有效的Connection對象變量名稱的變量型，或包含ConnectionString參數的字符串型。CursorType：可選，返回CursorTypeEnum值，用于確定在打開Recordset時提供程序應使用的游標的類型。CursorType參數詳解如表8.2所示29第8章-VBA數據庫編程30第8章-VBA數據庫編程舉例PublicSubcmdConnection()DimcnnAsNewADODB.Connection'聲明的時候直接實例化對象DimrstAsNewADODB.RecordsetDimstrSQLAsStringcnn.Provider="Microsoft.ACE.OLEDB.16.0"cnn.ConnectionString="F:\數據庫實例\學籍管理.accdb"cnn.OpenstrSQL="select*from教師"rst.OpenstrSQL,cnn,adOpenForwardOnly'獲得記錄集Debug.Printrst.GetStringcnn.CloseEndSub31第8章-VBA數據庫編程3．Recordset對象Recordset對象常用的屬性和方法32第8章-VBA數據庫編程表8.4 RecordSet對象常用屬性屬性說明Bof若為True，記錄指針指向記錄集的頂部（即指向第一個記錄之前）Eof若為True，記錄指針指向記錄集的底部（即指向最后一個記錄之后）RecordCount返回記錄集對象中的記錄個數表8.5 RecordSet對象常用方法方法說明Open打開一個RecordSet對象Close關閉一個RecordSet對象Update將RecordSet對象中的數據保存（即寫入）到數據庫Delete刪除RecordSet對象中的一個或多個記錄Find在RecordSet中查找滿足指定條件的行MoveFirst把記錄指針移到第一個記錄MoveLast把記錄指針移到最后一個記錄MoveNext把記錄指針移到下一個記錄MovePrevious把記錄指針移到前一個記錄8.4特殊域聚合函數和RunSQL方法1．特殊域聚合函數常用的特殊域聚合函數有DCount函數、DAvg函數、DSum函數、DMax函數、DMin函數和DLookup函數。

（1）DCount函數格式：DCount(表達式,記錄集[,條件表達式])例如，計算“學生”表中男同學的人數，語句為：n=DCount("[xh]","學生","[xb]='男'")33第8章-VBA數據庫編程（2）DAvg函數可以使用DAvg函數來計算特定記錄集內一組值的平均值。格式：DAvg(表達式,記錄集[,條件表達式])例如，計算“學生”表中男同學的平均高考成績，語句為：n=DAvg("[gkcj]","學生","[xb]='男'")（3）DSum函數可以使用DSum函數來計算特定記錄集內一組值的總和。格式：DSum(表達式,記錄集[,條件表達式])例如，計算“學生”表中男同學的高考成績總和，語句為：n=DSum("[gkcj]","學生","[xb]='男'")34第8章-VBA數據庫編程（4）DMax函數可以使用DMax函數來計算特定記錄集內一組值的最大值。格式：DMax(表達式,記錄集[,條件表達式])例如，計算“學生”表中男同學的高考成績最高分，語句為：n=DMax("[gkcj]","學生","[xb]='男'")（5）DMin函數可以使用DMin函數來計算特定記錄集內一組值的最小值。格式：DMin(表達式,記錄集[,條件表達式])例如，計算“學生”表中男同學的高考成績最低分，語句為：n=DMin("[gkcj]","學生","[xb]='男'")35第8章-VBA數據庫編程（6）DLookup函數可以使用DSum函數來計算特定記錄集內獲取特定字段的值。格式：DLookup(表達式,記錄集[,條件表達式])例如，獲取“學生”表中劉航同學所在的班級，語句為：n=DLookup("[bjmc]","學生","[xm]='劉航'")36第8章-VBA數據庫編程例如，窗體上有一個文本框控件（名稱為txtXH），在該控件中輸入學號，將來自于“學生”表中該學生的姓名顯示在另一個文本框控件（名稱為txtXM）中，語句為：Me!txtXM=DLookup("[xm]","學生","[xh]='"&Me!txtXH&"'")37第8章-VBA數據庫編程2．RunSQL方法DoCmd對象的RunSQL方法可以直接運行Access的操作查詢，完成對數據表記錄的操作。也可以運行數據定義語句實現表和索引的定義。格式為：RunSQL(SQLStatement,UseTransaction)例如，將所有學生的高考成績增加10分，語句為：DoCmd.RunSQL"update學生setgkcj=gkcj+10"38第8章-VBA數據庫編程8.5綜合案例39第8章-VBA數據庫編程【例8.4】對學籍管理數據庫中的教師表進行操作，能夠實現記錄的前后移動以及數據庫常用的增加、刪除或修改記錄的功能。其界面如圖8.5所示。界面布局如下：40第8章-VBA數據庫編程控件類型名稱標題控件類型名稱標題文本框txtgh工號：文本框txtxm姓名：文本框txtxb性別：文本框txtzc職稱：命令按鈕cmdFirst第一條命令按鈕cmdPrev向上命令按鈕cmdNext向下命令按鈕cmdLast最后一條命令按鈕cmdAdd添加命令按鈕cmdDel刪除命令按鈕cmdModi修改命令按鈕cmdExit退出關鍵代碼如下：41第8章-VBA數據庫編程OptionCompareDatabase'通用模塊中聲明并初始化ADO各個對象DimcnnAsNewADODB.ConnectionDimrstAsNewADODB.Recordset'增加命令按鈕單擊事件過程PrivateSubcmdAdd_Click()'工號為主鍵，不能夠為空,先判斷Iftxtgh<>""Thenrst.AddNew'增加空白記錄rst("gh")=Me!txtgh'將添加的內容保存在記錄集中rst("xm")=Me!txtxmrst("xb")=Me!txtxbrst("zc")=Me!txtzcrst.Update'更新數據庫ElseMsgBox"工號為主鍵不能夠為空！"EndIfEndSub'刪除命令按鈕單擊事件過程PrivateSubcmdDel_Click()rst.DeleteEndSub'退出命令按鈕單擊事件過程PrivateSubcmdExit_Click()rst.Closecnn.Close