筆者在上篇文章中詳細介紹了Excel VBA 連接配接資料庫時遇到的“巨坑”，非常無奈，一時無解，手足無措，奈何工作不能停，隻能另辟蹊徑，總算得到預期的結果，今展示給大家，以期今後少走彎路，當然仍然期待高手能夠完美解決上篇文章中遇到的“巨坑”，指點迷津，答疑解惑。閑言少續，直接進主題。
一、原始檔案
資料庫：corn
表：成品出庫
列：product
資料庫中的原始資料
二、遇到“巨坑”
查詢結果：product 列中的 “達育5158” 顯示不全，隻顯示“達育51”。如圖：
資料顯示不全
問題代碼：
Option Explicit
Sub GetDataFromMysql()
'1.設定變量
'前期綁定法
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, customer_name$
Dim sh As Worksheet
Dim i As Integer
'2.設定查詢結果存放的sheet表，這裡我選擇的是sheet2
Set sh = Sheets(2)
sh.Range("A1:K500").Clear
'3. 建立資料庫的連接配接
con.ConnectionString = "driver={MySQL ODBC 8.0 unicode Driver};server=localhost;uid=wyj;pwd=wyj;database=corn;port=3306;option=3"
con.Open
customer_name = Sheets(2).Range("M2").Value
sql = "select date,customer,address,product,小袋規格,大袋規格 from 成品出庫 where customer like '%" & customer_name & "%' "
rs.Open sql, con, adOpenStatic, adLockOptimistic
'4.設定表頭
'sh.Range("A1:E1").Value = Array("ID", "date", "name", "salary", "other")
For i = 0 To rs.Fields.Count - 1
sh.Cells(1, i + 1) = rs.Fields(i).name
Next i
'5.将資料輸出到sheet2工作表
sh.Range("A2").CopyFromRecordset rs
'6.關閉連接配接,釋放記憶體
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub
三、另辟蹊徑，解決問題
正确代碼：
Option Explicit
Sub query_mysql_toexcel()
Dim con As Object, objcon As Object, rs As Object
Dim sql As String, customer_name$, cnstr$
Dim driver$, host$, database$, user$, pwd$
Const adUseClient = 3
Set con = CreateObject("adodb.connection")
driver = "driver={MySQL ODBC 8.0 unicode Driver}"
host = "localhost"
database = "corn"
user = "wyj"
pwd = "wyj"
cnstr = driver & "; Server=" & host & ";Database=" & database & ";Uid=" & user & ";Pwd=" & pwd & ";option=3"
con.Open cnstr
customer_name = Sheets(2).Range("M2").Value
sql = "Select date,customer,address,product,小袋規格,大袋規格 From 成品出庫 where customer like '%" & customer_name & "%'"
Set rs = CreateObject("adodb.recordset")
rs.CursorLocation = adUseClient
rs.Open sql, con, 1, 3
Dim iRow, iCol As Integer, i As Integer
iRow = 2
Sheet2.Range("A1:K500").Clear
'設定表頭
For i = 0 To rs.Fields.Count - 1
Sheet2.Cells(1, i + 1) = rs.Fields(i).name
Next i
'拷貝資料
Sheet2.Range("A2").CopyFromRecordset rs
rs.Close
con.Close
Set rs = Nothing
Set con = Nothing
End Sub
運作結果：
結果正确
三、發現問題
經過與問題代碼逐行比對，最後終于發現不同之處，隻需要在問題代碼這一行：
rs.Open sql, con, adOpenStatic, adLockOptimistic
的上面加上：
rs.CursorLocation = adUseClient
即可得到正解結果。
這一問題讓我糾結了很長時間，真是功夫不負有心人，謹以此警示大家，不再被同樣的問題騷擾。