詳細(xì)信息請參考《MySQL 6.0 Reference Manual》的以下章節(jié)
28.1.5.7. Connector/ODBC Programming
28.1.5.7.1. Using Connector/ODBC with Visual Basic Using ADO, DAO and RDO
首先當(dāng)然是安裝MySQL ODBC Connector，然后是在VBA中添加引用
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library
Sub Export2Mysql()
'將Excel當(dāng)中的數(shù)據(jù)轉(zhuǎn)入數(shù)據(jù)庫中
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String
Set conn = New ADODB.Connection
'這里要換成你的服務(wù)器 庫名 用戶名 密碼
conn.ConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=server_ip;" & " DATABASE=dbname;" & "UID=user_id
WD=password; OPTION=3"
conn.Open
'準(zhǔn)備創(chuàng)建表
conn.Execute "drop table if exists test"
'注意這里的各列類型設(shè)定
conn.Execute "create table test(name text,pass text)"
'按行導(dǎo)入,這里假設(shè)第一列存的是name，第二列存的是pass
For i = 1 To 20
conn.Execute "insert into test(name,pass) values('" & Cells(i, 1).Text & "','" & Cells(i, 2) & "')"
Next i
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
'使用下面的代碼驗證
rs.Open "select * from test", conn
rs.MoveFirst
Do Until rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Value,
Next
rs.MoveNext
Debug.Print
Loop
rs.Close
conn.Close
End Sub
