Excel VBA 连接到 MySQL - 架构不匹配错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16752417/
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
Excel VBA connect to MySQL - architecture mismatch error
提问by Ramesh
I am trying to connect to MySQL from my local machine located on a server using VBA. Initially I was receiving the below error.
我正在尝试从位于使用 VBA 的服务器上的本地计算机连接到 MySQL。最初我收到以下错误。
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
[Microsoft][ODBC 驱动程序管理器] 未找到数据源名称且未指定默认驱动程序。
After some research, I figured out that the MySQL ODBC driver should be installed first. I installed the driver from the below location.
经过一番研究，我发现应该先安装 MySQL ODBC 驱动程序。我从以下位置安装了驱动程序。
http://dev.mysql.com/downloads/connector/odbc/
http://dev.mysql.com/downloads/connector/odbc/
I have a 64 bit machine and so I installed the driver for 64 bit and tried to establish the connection. Even then I was receiving the same data source name not found error. However from the ODBC data source administrator, if I select System DSN, I am able to see MySQL driver installed and I am able to create a new data source for my database in the server.
我有一台 64 位机器，所以我安装了 64 位驱动程序并尝试建立连接。即便如此，我还是收到了相同的数据源名称未找到错误。但是，从 ODBC 数据源管理员那里，如果我选择系统 DSN，我可以看到安装了 MySQL 驱动程序，并且我可以在服务器中为我的数据库创建一个新的数据源。
However from VBA, if I call the data source I receive another error.
但是，从 VBA 中，如果我调用数据源，则会收到另一个错误。
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.
[Microsoft][ODBC 驱动程序管理器] 指定的 DSN 包含驱动程序和应用程序之间的体系结构不匹配。
This is how I am calling the data source from my VBA.
这就是我从 VBA 调用数据源的方式。
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection
oConn.Open "data_source_name"
Also,for the 64 bit ODBC connector I am able to see two drivers as below in my System DSN.
此外，对于 64 位 ODBC 连接器，我可以在系统 DSN 中看到如下两个驱动程序。
- MySQL ODBC 5.2 ANSI Driver
- MySQL ODBC 5.2 Unicode Driver
- MySQL ODBC 5.2 ANSI 驱动程序
- MySQL ODBC 5.2 Unicode 驱动程序
For both of the drivers, my connection is successful from the ODBC data administrator.
对于这两个驱动程序，我从 ODBC 数据管理员那里连接成功。
I tried to figure out a solution for the architecture mismatch problem and I read if I create the DSN by running the ODBC application from the below location on a 64 bit machine, it might work.
我试图找出体系结构不匹配问题的解决方案，我读到如果我通过在 64 位机器上从以下位置运行 ODBC 应用程序来创建 DSN，它可能会工作。
C:\Windows\System32\odbcad32
C:\Windows\System32\odbcad32
However for this scenario too, I received the same architecture mismatch error.
但是对于这种情况，我也收到了相同的架构不匹配错误。
I even tried installing MySQL Connector/ODBC 5.2.5 32 bit in my machine. For this case, I am not able to see the driver listed in the system DSN.
我什至尝试在我的机器上安装 MySQL Connector/ODBC 5.2.5 32 位。对于这种情况，我无法看到系统 DSN 中列出的驱动程序。
Can someone help me figure out what am actually doing wrong?
有人可以帮我弄清楚实际上做错了什么吗？
采纳答案by Drew
I can select and loop thru results sets and do inserts. data verified in db. let me know if you need help
我可以选择并循环遍历结果集并进行插入。在 db 中验证的数据。如果您需要帮助，请告诉我
windows 7 Ultimate
version 6.1 (build 7601: service pack 1)
64 bit
************************************************************************
ODBC Data Source Administrator
run by command: %windir%\system32\odbcad32.exe
2 drivers:
MySQL ODBC 5.2 ANSI Driver , 5.02.05.00, Oracle, MYODBC5A.DLL, 4/4/2013
MySQL ODBC 5.2 Unicode Driver , 5.02.05.00, Oracle, MYODBC5W.DLL, 4/4/2013
************************************************************************
create System DSN, named hp
using Unicode Driver
dsn=hp
descr=hp
tcpip server=192.168.1.11
user=root
password=xxxxx
Database=test
************************************************************************
ODBC Data Source Admin tool
far right tab called About
Admin, Control Panel, Cursor Library, Driver Mgr, Localized R DLL, Unicode Cursor Lib all Version 6.1.7601-ish
************************************************************************
MSFT Office Professional Plus 2010
Version 14.0.6129.5000 (64 bit)
VBA 7.0
Tools Menu / References / References - VBAProject, scroll down, click on:
Microsoft ActiveX Data Objects 6.1 Library
References Location= c:\program files\common files\system\ado\msado15.dll
************************************************************************
code same, get into an excel Macro:
Sub Macro1()
'
' Macro1 Macro
'
Dim oConn As ADODB.Connection
Dim rsPass As ADODB.Recordset
Dim sql As String
Set oConn = New ADODB.Connection
oConn.Open "hp"
Set rsPass = New ADODB.Recordset
sql = "select * from charlie1"
rsPass.Open sql, oConn
rsPass.Close
sql = "insert into charlie1 (billybob,birthdate,funny_num) values (5,now(),383.111)"
rsPass.Open sql, oConn
End Sub
回答by iOSdevdude
Run Task Manager and look for EXCEL.EXE - most likely it has *32 after it (the issue is you are running a 32-bit version of excel, attempting to use the 64-bit version of MySQL Connector/ODBC).
运行任务管理器并查找 EXCEL.EXE - 很可能它后面有 *32（问题是您正在运行 32 位版本的 excel，试图使用 64 位版本的 MySQL 连接器/ODBC）。
To resolve:
解决：
- Be sure to remove any defined ODBC data sources (DSNs) before uninstalling the 64-bit driver (unable to remove if the driver is already uninstalled)
- Uninstall 64-bit MySQL Connector/ODBC driver
- Download and install 32-bit version of MySQL Connector/ODBC driver
- To setup DSN, see this http://forums.mysql.com/read.php?37,357786,360776#msg-360776
- 在卸载 64 位驱动程序之前，请务必删除任何定义的 ODBC 数据源 (DSN)（如果驱动程序已卸载，则无法删除）
- 卸载 64 位 MySQL 连接器/ODBC 驱动程序
- 下载并安装 32 位版本的 MySQL Connector/ODBC 驱动程序
- 要设置 DSN，请参阅此http://forums.mysql.com/read.php?37,357786,360776#msg-360776
Note: You may be able to have both the 64-bit and 32-bit drivers installed and therefore not require uninstall of the 64-bit version. I did not need both so I haven't tested whether it is possible to have both installed.
注意：您可能可以同时安装 64 位和 32 位驱动程序，因此不需要卸载 64 位版本。我不需要两者，所以我没有测试是否可以同时安装两者。
回答by Mike
I was recently fighting with this problem myself.
我最近自己也在与这个问题作斗争。
Taking advice from iOSdedude, I followed the link you originally posted, downloaded the 32-bit driver, and my ODBC connection started working again.
根据 iOSdedude 的建议，我按照您最初发布的链接下载了 32 位驱动程序，然后我的 ODBC 连接又开始工作了。
I am running Windows 7 on and my OS shows 64-bit under My Computer --> Properties --> System Info, so I was surprised to see that the 64-bit driver didn't work.
我在 Windows 7 上运行，我的操作系统在我的电脑 --> 属性 --> 系统信息下显示 64 位，所以我很惊讶地看到 64 位驱动程序不起作用。
Not a good explanation as to why this works, but it worked for me.
不能很好地解释为什么这有效，但它对我有用。
