VBA 如何在 Excel 中连接到 MySQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13558921/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can VBA connect to MySQL database in Excel?
提问by Tin Amaranth
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
Dim str As String
str = "DRIVER={MySQL ODBC 5.2.2 Driver};" & _
"SERVER=sql100.xtreemhost.com;" & _
"PORT=3306" & _
"DATABASE=xth_9595110_MyNotes;" & _
"UID=xth_9595110;" & _
"PWD=myPassword;" & _
"Option=3"
''' error '''
oConn.Open str
End Sub
Private Sub InsertData()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
ConnectDB
sql = "SELECT * FROM ComputingNotesTable"
rs.Open sql, oConn, adOpenDynamic, adLockOptimistic
Do Until rs.EOF
Range("A1").Select
ActiveCell = rs.Fields("Headings")
rs.MoveNext
Loop
rs.Close
oConn.Close
Set oConn = Nothing
Set rs = Nothing
End Sub
Doing the similar things in PHP, I could successfully log in to the MySQL server. I have installed the ODBC connector. But in the above VBA codes, I failed. An error turns up. (see the codes where the error exists)
在PHP中做类似的事情，我可以成功登录到MySQL服务器。我已经安装了 ODBC 连接器。但是在上面的 VBA 代码中，我失败了。出现错误。（请参阅存在错误的代码）
$connect = mysql_connect("sql100.xtreemhost.com","xth_9595110","myPassword") or die(mysql_error());
mysql_select_db("myTable",$connect);
采纳答案by Bob Davis
Ranjit's code caused the same error message as reported by Tin, but worked after updating Cn.open with the ODBC driver I'm running. Check the Drivers tab in the ODBC Data Source Administrator. Mine said "MySQL ODBC 5.3 Unicode Driver" so I updated accordingly.
Ranjit 的代码导致与 Tin 报告的错误消息相同的错误消息，但在使用我正在运行的 ODBC 驱动程序更新 Cn.open 后工作。检查 ODBC 数据源管理器中的驱动程序选项卡。我说“MySQL ODBC 5.3 Unicode 驱动程序”所以我相应地更新。
回答by Ranjit Kumar
This piece of vba worked for me:
这段vba对我有用：
Sub connect()
Dim Password As String
Dim SQLStr As String
'OMIT Dim Cn statement
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
'OMIT Dim rs statement
Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
Server_Name = Range("b2").Value
Database_name = Range("b3").Value ' Name of database
User_ID = Range("b4").Value 'id user or username
Password = Range("b5").Value 'Password
SQLStr = "SELECT * FROM ComputingNotesTable"
Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
Cn.Open "Driver={MySQL ODBC 5.2.2 Driver};Server=" & _
Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
Dim myArray()
myArray = rs.GetRows()
kolumner = UBound(myArray, 1)
rader = UBound(myArray, 2)
For K = 0 To kolumner ' Using For loop data are displayed
Range("a5").Offset(0, K).Value = rs.Fields(K).Name
For R = 0 To rader
Range("A5").Offset(R + 1, K).Value = myArray(K, R)
Next
Next
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
回答by aeroacoustic
Just a side note for anyone that stumbles onto this same inquiry... My Operating System is 64 bit - so of course I downloaded the 64 bit MySQL driver... however, my Office applications are 32 bit... Once I downloaded the 32 bit version, the error went away and I could move forward.
对于偶然发现相同查询的任何人来说，只是一个旁注......我的操作系统是 64 位 - 所以我当然下载了 64 位 MySQL 驱动程序......但是，我的 Office 应用程序是 32 位......一旦我下载了32 位版本，错误消失了，我可以继续前进。
回答by Andrew
Enable Microsoft ActiveX Data Objects 2.8 Library
启用 Microsoft ActiveX 数据对象 2.8 库
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=yourdatabase;" & _
"USER=yourdbusername;" & _
"PASSWORD=yourdbpassword;" & _
"Option=3"
End Sub
There rest is here: http://www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/
剩下的在这里：http: //www.heritage-tech.net/908/inserting-data-into-mysql-from-excel-using-vba/
回答by ChrCury78
Updating this topic with a more recent answer, solution that worked for me with version 8.0 of MySQL Connector/ODBC (downloaded at https://downloads.mysql.com/archives/c-odbc/):
使用更新的答案更新此主题，该解决方案适用于 MySQL Connector/ODBC 8.0 版（在https://downloads.mysql.com/archives/c-odbc/下载）：
Public oConn As ADODB.Connection
Sub MySqlInit()
If oConn Is Nothing Then
Dim str As String
str = "Driver={MySQL ODBC 8.0 Unicode Driver};SERVER=xxxxx;DATABASE=xxxxx;PORT=3306;UID=xxxxx;PWD=xxxxx;"
Set oConn = New ADODB.Connection
oConn.Open str
End If
End Sub
The most important thing on this matter is to check the proper name and version of the installed driver at: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers\
最重要的是在以下位置检查已安装驱动程序的正确名称和版本：HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers\
