用Excel VBA 存取 MySQL

MySQL（MariaDB）是一套非常流行的資料庫系統，因為它開源、免費，幾乎是每一個個人架站者的首選資料庫。

Microsoft Excel 更不用說，它是辦公室必備神器，老闆愛用來看報表，所以工程師們一定要會使用它。

今天我們要把 Excel資料寫入MySQL，除了用MySQL的一些管理軟體外，身為程式設計師的我們，當然要用 "VBA 來自動寫回 MySQL" 囉~~

接下來就是環境設定的基本步驟：

1.安裝 MySQL ODBC 驅動程式：

在google直接搜尋 "MySQL ODBC"

用 Google 搜尋 MySQL ODBC

我就可以找到 MySQL :: Developer Zone 點開它，

MySQL ODBC connector 下載

這邊 ORACLE 預設讓我們下載 for Windows X64 最新的 Connector 版本，但是千萬不要裝這個版本。。。。，請點上方的 "Archives"

MySQL ODBC connector archives

進到這一頁後，Product version: 就可以選取較古老的版本，請選取8.0以前的版本，因為我們要用的是 Windows (x86,32-bit)的版本，也不要是ansi版本

我這邊就選 5.2.6 win32的版本，檔案大小約 3.7MB

下載下來的 msi 安裝檔

安裝過程很簡單，就按同意後，一直Next，到最後按 Finish，就結束了。

2.測試 MySQL ODBC 連線：

安裝完 MySQL ODBC Connector後，我們就先來測試一下：

在開始選單中找到 "ODBC Data Sources(32-bit)"

ODBC 資源來源管理員(32位元)

點選「驅動程式」，向下拉到最底，就可以看到剛剛安裝的「MySQL ODBC 5.2 Unicode Driver」

接下來讓我們測試一下連線，讓我們再回到「使用者資料來源名稱」的頁面，

按下「新增(D)...」

按下「新增(D)...」後，同樣找到最後的「MySQL ODBC 5.2 Unicode Driver」

填入我們的MySQL伺服器資料。。。

僅需要輸入Name:、 TCP/IP Server: 、 User: 和 Password: 就可以按下「Test」

按下「Test」後，如果出現「Connection successful」就表示我們完成安裝的步驟了。

MySQL 連線成功

連線成功，在Database:選單中就可以看到我們在MySQL伺服器上的"資料庫"們

有「Database」，確認 MySQL ODBC 連線成功

測試完成後，可以直接按「Cancel」，再按「取消」關閉視窗就可以了。

3.Excel VBA 設定

Excel 設定的「信任中心」，務必將巨集啟用。

4.VBA 連線 MySQL 宣告：

 Set myConn = CreateObject("ADODB.Connection")
 myConn.Open "Driver={MySQL ODBC 5.2 Unicode Driver};Server=127.0.0.1;Database=equip_management;User=sa;Password='XXXXX';Option=3;"

先建立一個 "ADODB.Connection" 的物件

再用 Open 進行連線，

Driver={MySQL ODBC 5.2 Unicode Driver}; 務必填入你安裝的 MySQL ODBC Connector 版本，這裡可以到 "Windows系統管理工具 → ODBC 資料來源（32位元）→ 驅動程式" 查詢Driver名稱，

Server=127.0.0.1; 請填入MySQL伺服器的IP，相關MySQL設定以後再介紹，

Database=equip_management; 請填入我們要連線的資料庫名稱，

User=sa; 請填入我們的資料庫使用者名稱，

Password='XXXXX'; 請填入我們的資料庫使用者密碼，

Option=3; 這邊的Option=3 表示使用 Unicode 支援。

5.VBA 執行 MySQL 查詢資料:

簡易的MySQL讀取

// 使用 SQL查詢語法
SQL = "SELECT * FROM maintain_records WHERE 1 = 1"
SET myResult = myConn.Execute(SQL)				// 執行查詢

Sheets("MySQL_DB").Select									// 選擇 "MySQL_DB" Sheets
Cells.Select																// 選擇全部
Selection.ClearContents										// 清空資料
Range("A1").Select													// 選取 A1 的位置
	
Range("A1").CopyFromRecordset myResult		// 將查詢到的資料貼在A1的位置上

讀取 MySQL 資料

上圖似乎少了欄位名稱，於是我們再改一下:

// 使用 SQL查詢語法
SQL = "SELECT * FROM maintain_records WHERE 1 = 1"
SET myResult = myConn.Execute(SQL) // 執行查詢
Sheets("MySQL_DB").Select // 選擇 "MySQL_DB" Sheets
Cells.Select 								// 選擇全部
Selection.ClearContents 		// 清空資料

// Range("A1").Select // 選取 A1 的位置
// 輸出欄位名稱
For i = 1 To myResult.Fields.Count
  Cells(1, i).Value = myResult.Fields(i - 1).Name
Next i

Range("A2").CopyFromRecordset myResult // 將查詢到的資料貼在A1的位置上
myResult.Close		// 釋放 myResult 資料
myConn.Close			// 釋放 myConn 連線

有了欄位名稱看起來漂亮一點。。。。。

同樣的 VBA 執行 MySQL 的 "寫入、修改、刪除" 全都是用

SET myResult= myConn.Execute(SQL) 

重點就是 SQL 指令的拼湊，這裡就不多做贅述，自己多做嚐試吧。

6.為什麼用32位元的ODBC Driver

最後來解釋一下為什麼使用32位元的 ODBC Driver，

原因在於我們安裝的 Office 版本，

我電腦裝的是 Office 2016 32位元版

雖然說 Office 在 2010 以後開始有支援64位元版本，但為了相容性問題，一般微軟都會預設安裝 32位元版本，如果你安裝了 64位元版本的Office，也就要安裝 x64版的ODBC connector。

所以安裝MySQL ODBC connector 前，請確認好你的Office是32位元還是64位元。