Excel VBA connect to MySQL - architecture mismatch error

Question

I am trying to connect to MySQL from my local machine located on a server using VBA. Initially I was receiving the below error.

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

After some research, I figured out that the MySQL ODBC driver should be installed first. I installed the driver from the below location.

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.

However from VBA, if I call the data source I receive another error.

[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application.

This is how I am calling the data source from my 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.

MySQL ODBC 5.2 ANSI Driver
MySQL ODBC 5.2 Unicode Driver

For both of the drivers, my connection is successful from the ODBC data administrator.

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.

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.

Can someone help me figure out what am actually doing wrong?

Does this mean I cannot follow this approach to insert data into MySQL database currently? — Ramesh
– Ramesh, Commented May 25, 2013 at 18:13

Drew · Accepted Answer · 2013-05-25 20:07:30Z

3

I can select and loop thru results sets and do inserts. data verified in db. let me know if you need help

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

edited May 25, 2013 at 20:07

answered May 25, 2013 at 18:29

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ramesh Over a year ago

Hi Drew, Thanks again. But I still receive the same architecture mismatch error. I am using excel 2010 but not sure what's wrong.

Ramesh Over a year ago

Yeah..I have installed TeamViewer in my system now..I am not sure on how to setup a meeting..

Ramesh Over a year ago

Should I share my ID and password from Teamviewer to set up a remote control session in TeamViewer?

Ramesh Over a year ago

This is the team viewer ID.. 832 692 691..Pwd is 2903

Ramesh Over a year ago

let us continue this discussion in chat

iOSdevdude · Accepted Answer · 2013-10-30 13:27:38Z

3

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).

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

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.

edited Oct 30, 2013 at 13:27

answered Oct 30, 2013 at 13:09

iOSdevdude

414 bronze badges

Comments

Mike · Accepted Answer · 2014-02-12 17:35:44Z

0

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.

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.

Not a good explanation as to why this works, but it worked for me.

answered Feb 12, 2014 at 17:35

Mike

336 bronze badges

4 Comments

Ramesh Over a year ago

No, the problem is not to check the OS version but to check the OFFICE version. In a 64 bit OS, we would have installed a 32 bit OFFICE and that is why we need a 32 bit driver.

ApproachingDarknessFish Over a year ago

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.

Mike Over a year ago

@Ramesh That makes sense, but I don't totally understand why 32-bit office works on a 64-bit OS.

Mike Over a year ago

@ValekHalfHeart I tried to comment but it did not allow me because I had insufficient points. Thanks for the tip. I'll keep it in mind for future posts.

Collectives™ on Stack Overflow

Excel VBA connect to MySQL - architecture mismatch error

3 Answers 3

5 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related