Excel擅長(zhǎng)數(shù)據(jù)分析，而Access擅長(zhǎng)整理和存儲(chǔ)大量繁雜的數(shù)據(jù)。將Excel與Access整合起來(lái)協(xié)同運(yùn)用，發(fā)揮各自的優(yōu)勢(shì)，從而滿(mǎn)足更復(fù)雜的需求。

搭建環(huán)境

在你的電腦中，除安裝了Excel外，還需要安裝有Access 2007及以上版本的軟件。更重要的是，要在VBE中添加對(duì)ADO的引用：

• 在VBE中，單擊菜單欄中“工具”下的“引用”，在“引用”框中，選取“Microsoft ActiveX Data Objects ×.× Library”和“Microsoft ADO Ext. ×.× for DLL And Security”前的復(fù)選框，單擊“確定”。

Excel工作表如下圖所示：

代碼功能

下面的幾段代碼實(shí)現(xiàn)下列功能：

• 使用VBA創(chuàng)建Access數(shù)據(jù)庫(kù)

• 使用Excel中的數(shù)據(jù)填充數(shù)據(jù)庫(kù)

• 通過(guò)Excel添加/刪除字段，并填充/刪除相應(yīng)的數(shù)據(jù)

• 在Excel中獲取Access中的數(shù)據(jù)

• Excel中的數(shù)據(jù)修改后，Access數(shù)據(jù)庫(kù)實(shí)現(xiàn)更新

程序代碼及解析

創(chuàng)建并填充Access數(shù)據(jù)庫(kù)

下面的代碼首先創(chuàng)建一個(gè)Access數(shù)據(jù)庫(kù)及表，然后使用Excel工作表中的數(shù)據(jù)填充該數(shù)據(jù)庫(kù)表。

解析：

• 上述代碼創(chuàng)建一個(gè)新的Catalog對(duì)象，用于使用ADOX來(lái)引用數(shù)據(jù)庫(kù)。創(chuàng)建一個(gè)名為變量strTableName代表的值的表，并且為新創(chuàng)建的表添加字段，本例中添加了5個(gè)字段，你可以根據(jù)實(shí)際修改來(lái)增加或減少字段以及改變字段的名稱(chēng)；將新表添加到數(shù)據(jù)庫(kù)的Tables集合中。

• 接下來(lái)，代碼為新表創(chuàng)建主鍵。連接到已有的數(shù)據(jù)庫(kù)，刪除數(shù)據(jù)庫(kù)表中已有的主鍵，并創(chuàng)建新的主鍵。

• 隨后，基于數(shù)據(jù)表定義記錄集，每次遍歷工作表中的一行并將數(shù)據(jù)存放到Access數(shù)據(jù)庫(kù)表。其中，在循環(huán)中，rst.AddNew創(chuàng)建一條新記錄，然后遍歷每一列，將列中的值賦給記錄集中的每個(gè)字段，rst.Update保存記錄。

• 最后，代碼關(guān)閉Recordset對(duì)象和Connection對(duì)象并將對(duì)象變量設(shè)置為Nothing以清除內(nèi)存。

運(yùn)行代碼后，Access數(shù)據(jù)庫(kù)表如下圖所示：

添加新字段

如果在Excel工作表中增加了新標(biāo)題，例如“班級(jí)”（如下圖所示），那么在數(shù)據(jù)庫(kù)表中也要添加“班級(jí)”字段。

在A(yíng)ccess數(shù)據(jù)庫(kù)表中添加字段的代碼如下：

解析：

• 代碼首先創(chuàng)建新的ADO連接，然后創(chuàng)建ADOX Catalog對(duì)象（數(shù)據(jù)庫(kù)）并讓其使用ADO連接，接下來(lái)設(shè)置對(duì)想要修改的表的引用，并添加列。最后，清除引用對(duì)象。

運(yùn)行代碼后的數(shù)據(jù)庫(kù)表如下：

也可以使用SQL語(yǔ)句來(lái)添加字段，代碼如下：

解析：

• 代碼首先創(chuàng)建新的ADO連接，然后創(chuàng)建Command對(duì)象并讓其使用ADO連接，接著執(zhí)行SQL語(yǔ)句來(lái)添加新字段，最后清除對(duì)象引用。

為新字段添加數(shù)據(jù)

下面的代碼為剛才添加的字段填充數(shù)據(jù)：

解析：

• 需要遍歷工作表中的行，每次修改一條記錄中的字段內(nèi)容。

• 代碼創(chuàng)建ADO連接，根據(jù)主鍵值逐條選取記錄并打開(kāi)，然后修改記錄，保存并關(guān)閉記錄集，最后關(guān)閉連接并清除對(duì)象引用。

結(jié)果如下圖所示：

刪除字段

下面的代碼刪除上文中添加的“班級(jí)”字段：

解析：

• 與前面的代碼一樣，首先創(chuàng)建新的ADO連接并創(chuàng)建ADOX對(duì)象使之使用剛創(chuàng)建的ADO連接，然后設(shè)置對(duì)想要修改的數(shù)據(jù)庫(kù)表的引用，接著通過(guò)從表的Columns集合中刪除指定的列，最后清除對(duì)象引用并關(guān)閉連接。

也可以使用SQL查詢(xún)語(yǔ)句來(lái)實(shí)現(xiàn)，代碼如下：

解析：

• 代碼創(chuàng)建新的ADO連接，隨后創(chuàng)建一個(gè)Command對(duì)象并使之使用新的ADO連接，然后執(zhí)行帶有DROP子句的SQL語(yǔ)句來(lái)刪除指定字段，最后清除對(duì)象并關(guān)閉連接。

關(guān)于數(shù)據(jù)庫(kù)的簡(jiǎn)單說(shuō)明

• 術(shù)語(yǔ)“記錄”和“字段”通常用于描述數(shù)據(jù)庫(kù)中的數(shù)據(jù)。一條記錄即表中的一行，一個(gè)字段即表中的一列。

• SQL即結(jié)構(gòu)化查詢(xún)語(yǔ)言，語(yǔ)法簡(jiǎn)單但功能強(qiáng)大，可以對(duì)數(shù)據(jù)庫(kù)實(shí)現(xiàn)各種操作。

• Connection對(duì)象用于在應(yīng)用程序和所要訪(fǎng)問(wèn)的數(shù)據(jù)源之間提供傳遞途徑。上文中的示例在使用數(shù)據(jù)庫(kù)前，先要建立應(yīng)用程序和數(shù)據(jù)庫(kù)之間的連接。

• Recordset對(duì)象的ActiveConnection屬性將現(xiàn)有的Connection對(duì)象賦給Recordset對(duì)象或者代表記錄集的一個(gè)連接字符串，以在數(shù)據(jù)庫(kù)中使用。若將一個(gè)連接字符串賦值給該屬性，則此記錄集將為自已創(chuàng)建一個(gè)Connection對(duì)象，用于引用該記錄集所使用的Connection對(duì)象。

  其CursorLocation屬性指定是用服務(wù)器端游標(biāo)引擎還是用客戶(hù)端游標(biāo)引擎 來(lái)操作記錄集中的記錄。

 在使用Open方法打開(kāi)數(shù)據(jù)庫(kù)時(shí)，如果其Source設(shè)置為從表中讀取記錄集（表名必須放置在雙引號(hào)中），則其Options參數(shù)設(shè)置為adCmdTable；如果設(shè)置為從SQL查詢(xún)語(yǔ)句讀取記錄集，則其Options參數(shù)設(shè)置為adCmdText。其CursorType參數(shù)在獲取記錄數(shù)據(jù)時(shí)使用adDynamic或adForwardOnly，且adForwardOnly在獲取大型數(shù)據(jù)集時(shí)性能更好；在寫(xiě)入記錄時(shí)，使用adOpenKeyset。

• Command對(duì)象用于執(zhí)行操作查詢(xún)，對(duì)數(shù)據(jù)源執(zhí)行插入、更新、刪除等操作。

太長(zhǎng)了！明天繼續(xù)......

轉(zhuǎn)載本文請(qǐng)聯(lián)系我或者注明出處。