使用 ODBC 连接器的 VBA/MySQL 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9818365/
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
VBA/MySQL issue using ODBC connector
提问by ploddingOn
I have been struggling with this for a few days now. Any help much appreciated.
我已经为此苦苦挣扎了几天。非常感谢任何帮助。
Trying to connect to MySQL database using Excel VBA, on a PC with the following:
尝试在具有以下内容的 PC 上使用 Excel VBA 连接到 MySQL 数据库：
Excel 2007 Windows 7 x64 Home Premium MySQL 5.5 MySQL ODBC Connector 5.1, 64 bit
Excel 2007 Windows 7 x64 家庭高级版 MySQL 5.5 MySQL ODBC 连接器 5.1，64 位
In the Excel VBA I have referenced Microsoft ActiveX Objects 2.8 Library.
在 Excel VBA 中，我引用了 Microsoft ActiveX Objects 2.8 库。
The VBA I am using to connect is:
我用来连接的 VBA 是：
Dim oConn As ADODB.Connection
Public Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & "SERVER=localhost;" & "DATABASE=test;" & "USER=root;" & "PASSWORD=PWhere;" & "Option=3"
End Sub
Every time I run this I get the error dialog: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"
每次运行时，我都会收到错误对话框：“[Microsoft][ODBC 驱动程序管理器] 未找到数据源名称且未指定默认驱动程序”
MySQL service is definitely running.
MySQL 服务肯定正在运行。
I have used Windows Data Source Administrator to check that MySQL ODBC Connector 5.1 is present and correct: it is, checks out OK when I try to create a DSN in this way.
我已使用 Windows 数据源管理器检查 MySQL ODBC 连接器 5.1 是否存在且正确：当我尝试以这种方式创建 DSN 时，检查结果是否正确。
Looking through the VBA project reference options, I note the options to reference a whole host of different ADO Libraries, including (Multi-dimensional) options and library versions 2.0,2.1,2.5,2.6,2.7, and 6.0 - maybe the answer lies in one of these?
查看 VBA 项目引用选项，我注意到引用大量不同 ADO 库的选项，包括（多维）选项和库版本 2.0、2.1、2.5、2.6、2.7 和 6.0 - 也许答案在于其中之一？
Any more details required, let me know.
需要更多详细信息，请告诉我。
采纳答案by assylias
You need to use either the 32 or 64 bits version depending on the version of Excel, not Windows. So even if you run Windows 7 64 bits, I believe Excel 2007 only comes in 32 bits so you would need to use the 32 bits mysql connector.
您需要使用 32 位或 64 位版本，具体取决于Excel的版本，而不是 Windows。因此，即使您运行 Windows 7 64 位，我相信 Excel 2007 也只有 32 位，因此您需要使用 32 位 mysql 连接器。
See also this bug reportthat is similar to your issue.
另请参阅与您的问题类似的错误报告。
回答by dink
I got similar message when moved my application to other system with different version of driver - it looks like misspelled driver name causes identical message. To find correct driver name and make application driver version independent I use the following code:
将我的应用程序移动到具有不同版本驱动程序的其他系统时，我收到了类似的消息 - 看起来拼写错误的驱动程序名称会导致相同的消息。要找到正确的驱动程序名称并使应用程序驱动程序版本独立，我使用以下代码：
Public Function Get_Driver() As String
Const HKEY_LOCAL_MACHINE = &H80000002
Dim l_Registry As Object
Dim l_RegStr As Variant
Dim l_RegArr As Variant
Dim l_RegValue As Variant
Get_Driver = ""
Set l_Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\.\root\default:StdRegProv")
l_Registry.enumvalues HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", l_RegStr, l_RegArr
For Each l_RegValue In l_RegStr
If InStr(1, l_RegValue, "MySQL ODBC", vbTextCompare) > 0 Then
Get_Driver = l_RegValue
Exit For
End If
Next
Set l_Registry = Nothing
End Function
