3

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?

Community
  • 1
  • 1
Ramesh
  • 765
  • 7
  • 24
  • 52

3 Answers3

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
Drew
  • 24,851
  • 10
  • 43
  • 78
  • 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 May 25 '13 at 18:33
  • Yeah..I have installed TeamViewer in my system now..I am not sure on how to setup a meeting.. – Ramesh May 25 '13 at 18:43
  • Should I share my ID and password from Teamviewer to set up a remote control session in TeamViewer? – Ramesh May 25 '13 at 18:54
  • This is the team viewer ID.. 832 692 691..Pwd is 2903 – Ramesh May 25 '13 at 20:39
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/30633/discussion-between-ramesh-and-drew-pierce) – Ramesh May 25 '13 at 20:45
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:

  1. 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)
  2. Uninstall 64-bit MySQL Connector/ODBC driver
  3. Download and install 32-bit version of MySQL Connector/ODBC driver
  4. 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.

iOSdevdude
  • 41
  • 4
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.

Mike
  • 33
  • 6
  • 1
    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. – Ramesh Feb 12 '14 at 17:38
  • 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](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – ApproachingDarknessFish Feb 12 '14 at 18:04
  • @Ramesh That makes sense, but I don't totally understand why 32-bit office works on a 64-bit OS. – Mike Feb 13 '14 at 19:07
  • @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. – Mike Feb 13 '14 at 19:09