經典代碼解析4.1：使用ADO整合Excel與Access

07-28

Excel擅長數據分析，而Access擅長整理和存儲大量繁雜的數據。將Excel與Access整合起來協同運用，發揮各自的優勢，從而滿足更複雜的需求。

搭建環境

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

在VBE中，單擊菜單欄中「工具」下的「引用」，在「引用」框中，選取「Microsoft ActiveX Data Objects ×.× Library」和「Microsoft ADO Ext. ×.× for DLL And Security」前的複選框，單擊「確定」。

Excel工作表如下圖所示：

代碼功能

下面的幾段代碼實現下列功能：

使用VBA創建Access資料庫

使用Excel中的數據填充資料庫

通過Excel添加/刪除欄位，並填充/刪除相應的數據

在Excel中獲取Access中的數據

Excel中的數據修改後，Access資料庫實現更新

程序代碼及解析

創建並填充Access資料庫

下面的代碼首先創建一個Access資料庫及表，然後使用Excel工作表中的數據填充該資料庫表。

解析：

上述代碼創建一個新的Catalog對象，用於使用ADOX來引用資料庫。創建一個名為變數strTableName代表的值的表，並且為新創建的表添加欄位，本例中添加了5個欄位，你可以根據實際修改來增加或減少欄位以及改變欄位的名稱；將新表添加到資料庫的Tables集合中。

接下來，代碼為新表創建主鍵。連接到已有的資料庫，刪除資料庫表中已有的主鍵，並創建新的主鍵。

隨後，基於數據表定義記錄集，每次遍歷工作表中的一行並將數據存放到Access資料庫表。其中，在循環中，rst.AddNew創建一條新記錄，然後遍歷每一列，將列中的值賦給記錄集中的每個欄位，rst.Update保存記錄。

最後，代碼關閉Recordset對象和Connection對象並將對象變數設置為Nothing以清除內存。

運行代碼後，Access資料庫表如下圖所示：

添加新欄位

如果在Excel工作表中增加了新標題，例如「班級」（如下圖所示），那麼在資料庫表中也要添加「班級」欄位。

在Access資料庫表中添加欄位的代碼如下：

解析：

代碼首先創建新的ADO連接，然後創建ADOX Catalog對象（資料庫）並讓其使用ADO連接，接下來設置對想要修改的表的引用，並添加列。最後，清除引用對象。

運行代碼後的資料庫表如下：

也可以使用SQL語句來添加欄位，代碼如下：

解析：

代碼首先創建新的ADO連接，然後創建Command對象並讓其使用ADO連接，接著執行SQL語句來添加新欄位，最後清除對象引用。

為新欄位添加數據

下面的代碼為剛才添加的欄位填充數據：

解析：

需要遍歷工作表中的行，每次修改一條記錄中的欄位內容。

代碼創建ADO連接，根據主鍵值逐條選取記錄並打開，然後修改記錄，保存並關閉記錄集，最後關閉連接並清除對象引用。

結果如下圖所示：

刪除欄位

下面的代碼刪除上文中添加的「班級」欄位：

解析：

與前面的代碼一樣，首先創建新的ADO連接並創建ADOX對象使之使用剛創建的ADO連接，然後設置對想要修改的資料庫表的引用，接著通過從表的Columns集合中刪除指定的列，最後清除對象引用並關閉連接。

也可以使用SQL查詢語句來實現，代碼如下：

解析：

代碼創建新的ADO連接，隨後創建一個Command對象並使之使用新的ADO連接，然後執行帶有DROP子句的SQL語句來刪除指定欄位，最後清除對象並關閉連接。

關於資料庫的簡單說明

術語「記錄」和「欄位」通常用於描述資料庫中的數據。一條記錄即表中的一行，一個欄位即表中的一列。

SQL即結構化查詢語言，語法簡單但功能強大，可以對資料庫實現各種操作。

Connection對象用於在應用程序和所要訪問的數據源之間提供傳遞途徑。上文中的示例在使用資料庫前，先要建立應用程序和資料庫之間的連接。

Recordset對象的ActiveConnection屬性將現有的Connection對象賦給Recordset對象或者代表記錄集的一個連接字元串，以在資料庫中使用。若將一個連接字元串賦值給該屬性，則此記錄集將為自已創建一個Connection對象，用於引用該記錄集所使用的Connection對象。

其CursorLocation屬性指定是用伺服器端游標引擎還是用客戶端游標引擎來操作記錄集中的記錄。

在使用Open方法打開資料庫時，如果其Source設置為從表中讀取記錄集（表名必須放置在雙引號中），則其Options參數設置為adCmdTable；如果設置為從SQL查詢語句讀取記錄集，則其Options參數設置為adCmdText。其CursorType參數在獲取記錄數據時使用adDynamic或adForwardOnly，且adForwardOnly在獲取大型數據集時性能更好；在寫入記錄時，使用adOpenKeyset。

Command對象用於執行操作查詢，對數據源執行插入、更新、刪除等操作。

太長了！明天繼續......

轉載本文請聯繫我或者註明出處。