《access數(shù)據(jù)庫(kù)設(shè)計(jì)》課件11章 VBA數(shù)據(jù)庫(kù)編程

第10章VBA數(shù)據(jù)庫(kù)編程主要內(nèi)容：VBA數(shù)據(jù)庫(kù)編程技術(shù)數(shù)據(jù)庫(kù)引擎及其體系結(jié)構(gòu)數(shù)據(jù)庫(kù)訪問(wèn)接口數(shù)據(jù)訪問(wèn)對(duì)象（DAO）ActiveX數(shù)據(jù)對(duì)象（ADO）VBA數(shù)據(jù)庫(kù)編程技術(shù)數(shù)據(jù)訪問(wèn)對(duì)象DAO的編程套路ActiveX數(shù)據(jù)對(duì)象（ADO）的編程套路數(shù)據(jù)處理中的幾個(gè)函數(shù)10.1VBA數(shù)據(jù)庫(kù)編程技術(shù)10.1.1數(shù)據(jù)庫(kù)引擎及其體系結(jié)構(gòu)所謂數(shù)據(jù)庫(kù)引擎實(shí)際上是一組動(dòng)態(tài)鏈接庫(kù)（DLL），當(dāng)程序運(yùn)行時(shí)被連接到VBA程序而實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的數(shù)據(jù)訪問(wèn)功能。數(shù)據(jù)庫(kù)引擎是應(yīng)用程序與物理數(shù)據(jù)庫(kù)之間的橋梁，它以一種通用接口的方式，使各種類(lèi)型物理數(shù)據(jù)庫(kù)對(duì)用戶而言都具有統(tǒng)一的形式和相同的數(shù)據(jù)訪問(wèn)與處理方法。Access2010的概念圖10.1.2數(shù)據(jù)庫(kù)訪問(wèn)接口（1）開(kāi)放數(shù)據(jù)庫(kù)互連應(yīng)用編程接口（ODBCAPI）

Windows為各種數(shù)據(jù)庫(kù)都提供了32位或64位ODBC驅(qū)動(dòng)程序，以實(shí)現(xiàn)應(yīng)用程序的訪問(wèn)。ODBC是基于Windows平臺(tái)較老的一種數(shù)據(jù)庫(kù)訪問(wèn)方式，目前中數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的開(kāi)發(fā)中已經(jīng)很少使用。（2）數(shù)據(jù)訪問(wèn)對(duì)象（DAO）DAO提供了一個(gè)數(shù)據(jù)庫(kù)訪問(wèn)的對(duì)象模型。利用一組數(shù)據(jù)庫(kù)訪問(wèn)對(duì)象，如Database、RecordSet等，實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的各種操作。DAO適用于單系統(tǒng)應(yīng)用程序或小范圍本地應(yīng)用的使用。（3）Active數(shù)據(jù)對(duì)象（ADO）ADO是基于組件的數(shù)據(jù)庫(kù)編程接口，是一個(gè)和編程語(yǔ)言無(wú)關(guān)的COM組件系統(tǒng)。使用ADO能方便訪問(wèn)任何符合ODBC標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)。ADO是DAO的后繼者，簡(jiǎn)單易用，成為當(dāng)前數(shù)據(jù)庫(kù)開(kāi)發(fā)的主流技術(shù)。目前最新的ADO版本是基于微軟.NET框架下的ADO.NET。10.1.3數(shù)據(jù)訪問(wèn)對(duì)象（DAO）1.DAO模型結(jié)構(gòu)Access2010中的DAO引用方法如下：（1）打開(kāi)Access數(shù)據(jù)庫(kù)窗口，單擊“數(shù)據(jù)庫(kù)工具”選項(xiàng)卡下“宏”命令組中的“VisualBasic”按鈕，進(jìn)入VBE編程環(huán)境。（2）單擊“工具”菜單欄，在彈出的下拉菜單中選擇“引用”選項(xiàng)。（3）在打開(kāi)的“引用”對(duì)話框中，勾選“MicrosoftDAO3.6ObjectLibrary”選項(xiàng)，并單擊“確定”按鈕。DAO模型層次簡(jiǎn)圖DAO各對(duì)象說(shuō)明表2.DAO對(duì)象變量的聲明和賦值DAO對(duì)象必須通過(guò)VBA程序代碼來(lái)控制和操作。在代碼中，必須設(shè)置對(duì)象變量，然后再通過(guò)對(duì)象變量使用其下的對(duì)象、或者對(duì)象的屬性和方法。（1）對(duì)象變量的聲明

同普通變量的聲明一樣，聲明的關(guān)鍵字可以是Dim、Private、Public等。

聲明對(duì)象變量的語(yǔ)句格式：Dim對(duì)象變量名As對(duì)象類(lèi)型

例如：DimwksAsWorkspace‘聲明wks為工作區(qū)對(duì)象變量DimdbsAsDatabase‘聲明dbs為數(shù)據(jù)庫(kù)對(duì)象變量（2）對(duì)象變量的賦值Dim只是聲明了對(duì)象變量的類(lèi)型，對(duì)象變量的值必須通過(guò)Set賦值語(yǔ)句來(lái)賦值。Set賦值語(yǔ)句的格式：

Set對(duì)象變量名稱=對(duì)象指定聲明例如：Setwks=DBEngine.Workspaces(0)‘打開(kāi)默認(rèn)工作區(qū)（即0號(hào)工作區(qū)）Setdbs=wks.OpenDatabase("e:\Access\職工管理.mdb")‘打開(kāi)數(shù)據(jù)庫(kù)10.1.4ActiveX數(shù)據(jù)對(duì)象（ADO）ADO又稱OLE自動(dòng)化接口，是訪問(wèn)由Microsoft推出的最新、功能最強(qiáng)的應(yīng)用程序接口。它是一種ActiveX對(duì)象，采用了被稱為OLEDB的數(shù)據(jù)訪問(wèn)模式。OLEDB與開(kāi)放式數(shù)據(jù)庫(kù)很相似，是一個(gè)便于使用的新的低層接口，以統(tǒng)一的方式訪問(wèn)存儲(chǔ)在不同信息源中的數(shù)據(jù)（包括關(guān)聯(lián)和非關(guān)聯(lián)數(shù)據(jù)庫(kù)，電子郵件和文件系統(tǒng)，文本和圖形，自定義商業(yè)對(duì)象等）。使用ADO以后，OLEDB的使用變得更簡(jiǎn)單。ADO是基于組件的數(shù)據(jù)庫(kù)編程接口，它是一個(gè)和編程語(yǔ)言無(wú)關(guān)的COM組件系統(tǒng)。使用ADO能方便地訪問(wèn)任何符合ODBC標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)。1.ADO對(duì)象模型如果需要在Access模塊設(shè)計(jì)的VBA代碼中使用ADO對(duì)象，必須首先增加Access系統(tǒng)對(duì)ADO庫(kù)的引用。設(shè)置引用庫(kù)的方法與DAO一致，ADO引用對(duì)話框如圖所示。ADO對(duì)象模型圖如圖所示，它提供一系列數(shù)據(jù)對(duì)象供使用。使用時(shí)，只需在程序中創(chuàng)建對(duì)象變量，并通過(guò)對(duì)象變量來(lái)調(diào)用訪問(wèn)對(duì)象方法、設(shè)置訪問(wèn)對(duì)象屬性，這樣就實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的各項(xiàng)訪問(wèn)操作。ADO只需要九個(gè)對(duì)象和四個(gè)集合（對(duì)象）就能提供其整個(gè)功能。ADO對(duì)象模型定義了一個(gè)可編程的分層對(duì)象集合，主要由Connection、Command和Recordset三個(gè)對(duì)象成員以及Error、Parameters和Fields等集合對(duì)象組成。各對(duì)象的主要功能如表所示。2.ADO對(duì)象變量的聲明ADO對(duì)象必須通過(guò)VBA程序代碼來(lái)控制和操作。在代碼中，必須設(shè)置對(duì)象變量，然后再通過(guò)對(duì)象變量使用其下的對(duì)象、或者對(duì)象的屬性和方法。聲明對(duì)象變量的語(yǔ)句格式：

Dim對(duì)象變量名稱AsADODB.對(duì)象類(lèi)型例如：DimconAsNewADODB.Connection‘聲明一個(gè)連接對(duì)象變量DimresAsNewADODB.RecordSet‘聲明一個(gè)記錄集對(duì)象變量3.主要ADO對(duì)象的使用方法（1）連接數(shù)據(jù)源。利用Connection對(duì)象可以創(chuàng)建一個(gè)數(shù)據(jù)源的連接，并利用其Open方法打開(kāi)該連接。（2）打開(kāi)記錄集對(duì)象或執(zhí)行查詢。數(shù)據(jù)庫(kù)連接后，可以利用Recordset對(duì)象打開(kāi)記錄集，并對(duì)記錄集中的數(shù)據(jù)進(jìn)行各種操作。（3）使用記錄集。使用記錄集包括記錄指針的移動(dòng)和記錄的操作。（4）關(guān)閉連接或記錄集。在應(yīng)用程序結(jié)束之前，應(yīng)該關(guān)閉并釋放分配給ADO對(duì)象（一般為Connection對(duì)象和Recordset對(duì)象）資源。關(guān)閉連接或記錄集使用的方法為Close方法。（5）重要屬性。利用Recordset記錄集對(duì)象的相關(guān)屬性，能夠判斷當(dāng)前記錄集的狀態(tài)。10.2VBA數(shù)據(jù)庫(kù)編程技術(shù)Access環(huán)境下的數(shù)據(jù)庫(kù)編程大致可以劃分為以下三種情況：（1）利用VBA+ADO（或DAO）操作當(dāng)前數(shù)據(jù)庫(kù)。（2）利用VBA+ADO（或DAO）操作本地?cái)?shù)據(jù)庫(kù)（ACCESS數(shù)據(jù)庫(kù)或其他）。（3）利用VBA+ADO（或DAO）操作遠(yuǎn)端數(shù)據(jù)庫(kù)（ACCESS數(shù)據(jù)庫(kù)或其他）。10.2.1數(shù)據(jù)訪問(wèn)對(duì)象DAO的編程套路‘定義對(duì)象變量DimwsAsDAO.WorkspaceDimdbAsDAO.DatabaseDimrsAsDAO.Recordset‘設(shè)置對(duì)象變量的值Setws=DBEngine.Workspaces(0)‘默認(rèn)工作區(qū)Setdb=ws.OpenDatabase(<數(shù)據(jù)庫(kù)文件名>)‘Setdb=CurrentDB()直接打開(kāi)Access中的當(dāng)前數(shù)據(jù)庫(kù)Setrs=db.OpenRecordset(<表名、查詢名或SQL語(yǔ)句>)DoWhileNotrs.EOF......rs.MoveNextLoop‘關(guān)閉記錄集、數(shù)據(jù)庫(kù)，釋放資源rs.Closedb.CloseSetrs=NothingSetdb=Nothing【例10.2】使用子過(guò)程SetAgePlusl將當(dāng)前Access數(shù)據(jù)庫(kù)中的學(xué)生基本信息表的“年齡”字段都加1SubSetAgePlusl（）‘定義對(duì)象變量DimdbAsDAO.Database‘?dāng)?shù)據(jù)庫(kù)對(duì)象DimrsAsDAO.Recordset‘記錄集對(duì)象DimfdAsDAO.Field‘字段對(duì)象‘注意：如果操作當(dāng)前數(shù)據(jù)庫(kù)，可用Setdb=CurrentDb（）來(lái)替換下面兩條語(yǔ)句Setdb=CurrentDb()‘打開(kāi)當(dāng)前數(shù)據(jù)庫(kù)Setrs=db.OpenRecordset（"學(xué)生基本信息表"）‘返回“學(xué)生基本信息表”表記錄集Setfd=rs.Fields（"年齡"）‘對(duì)記錄集是用循環(huán)結(jié)構(gòu)進(jìn)行遍歷DoWhileNotrs.EOFrs.Editfd=fd+1rs.Updaters.MoveNextLoop‘關(guān)閉并回收對(duì)象變量rs.Closedb.CloseSetrs=NothingSetdb=NothingEndSub10.2.2

ActiveX數(shù)據(jù)對(duì)象（ADO）的編程套路總體思路：(1)定義和創(chuàng)建ADO對(duì)象實(shí)例變量；(2)打開(kāi)數(shù)據(jù)庫(kù)連接－Connection(3)設(shè)置命令參數(shù)并執(zhí)行命令－Command(4)返回記錄集－SELECT語(yǔ)句(5)不返回記錄集-DELETE、UPDATE、INSERT(6)設(shè)置查詢參數(shù)并打開(kāi)記錄集－Recordset(7)操作記錄集(8)關(guān)閉、回收相關(guān)對(duì)象【例10.3】學(xué)生管理系統(tǒng)數(shù)據(jù)庫(kù)有數(shù)據(jù)表“教師基本信息”，其中有“教師編號(hào)”、“教師姓名”、“性別”和“職稱”4個(gè)字段。下面程序的功能是：通過(guò)窗體向“教師基本信息”表中添加教師記錄。對(duì)應(yīng)“教師編號(hào)”、“教師姓名”、“性別”和“職稱”4個(gè)文本框的名稱分別為：tNo、tName、tSex和tTitle。當(dāng)單擊窗體上的“添加”命令按鈕（名稱為Command1）時(shí)，首先判斷編號(hào)是否重復(fù)，如果不重復(fù)，則向教師基本信息表中添加教師記錄；如果編號(hào)重復(fù)，則給出提示信息。主要代碼如下：PrivateADOcnAsNewADODB.Connection‘ADOcn定義為ADODB連接對(duì)象PrivateSubForm_Load()‘打開(kāi)窗口時(shí)，連接Access本地?cái)?shù)據(jù)庫(kù) SetADOcn=CurrentProject.Connection‘初始化為連接當(dāng)前數(shù)據(jù)庫(kù)時(shí)要使用此語(yǔ)句EndSubPrivateSubCommand1_Click() ‘追加教師記錄 DimstrSQLAsString DimADOcmdAsNewADODB.Command DimADOrsAsNewADODB.Recordset SetADOrs.ActiveConnection=ADOcn

ADOrs.Open“Select教師編號(hào)From教師基本信息Where教師編號(hào)=‘”+tNo+”’” IfNotADOrs.EOFThen MsgBox“你輸入的教師編號(hào)已經(jīng)存在，不能添加！” Else ADOcmd.ActiveConnection=ADOcnstrSQL=“InsertInto教師基本信息表(教師編號(hào),教師姓名,性別,職稱)”strSQL=strSQL+“Values(‘”+tNo+“’,’”+tName+“’,’”+tSex+“’,’”+tTitles+“’)”ADOcmd.CommandText=strSQLADOcmd.ExecuteMsgBox“添加成功，請(qǐng)繼續(xù)！” EndIf ADOrs.Close SetADOrs=NothingEndSub10.2.3數(shù)據(jù)處理中的幾個(gè)函數(shù)（1）Nz函數(shù)將Null值轉(zhuǎn)換為0、空字符串””或者其他指定的值，調(diào)用格式為：Nz(表達(dá)式,指定值)（2）DCount函數(shù),DAvg函數(shù),DSum函數(shù)

調(diào)用格式和含義如下：DCount(字段,記錄集,條件式)：返回記錄集（表名或SQL語(yǔ)句）中的記錄數(shù)；DAvg(字段,記錄集,條件式)：返回記錄集中某字段的平均值；DSum(字段,記錄集,條件式)：返回記錄集中某字段的和；（3）DMax函數(shù)和DMin函數(shù)DMax(字段,記錄集,條件式)：返回記錄集（表名或SQL語(yǔ)句）某個(gè)字段列數(shù)據(jù)的最大值；DAvg(字段,記錄集,條件式)：返回記錄集（表名或SQL語(yǔ)句）某個(gè)字段列數(shù)據(jù)的最小值；（4）DLookup函數(shù)DLookup(字段,記錄集,條件式)：從指定記錄集里檢索特定字段的值