CODEWITHSUNDEEP
mysqlconnection-string
stighy
stighy

Reputation: 7170

Which connection string for MySql ODBC connector 5.2.6?

it seems i can't make work a connection to MySql using ODBC connector 5.2.6. In a 64 bit environment, in a VBA excel application, i use this string, but it not work:

"Driver={MySQL ODBC 5.2 Driver}; Server=myserver;Database=mydb;User=readonly;Password=mypass;Option=3"

I have also used

Driver={MySQL ODBC 5.2w Driver}

and

Driver={MySQL ODBC 5.2a Driver}

But the error is: ODBC driver unknow.

Can someone help me ?

Ps: it works with a DSN setted, but i would like to use a connection string so i don't go to each user computer and set a DSN.

Thanks

Upvotes: 0

Views: 12901

Answers (2)

Jack
Jack

Reputation: 11

I had a similar problem with Access 2010/2013 and windows 7. I assumed that I needed the 64 bit driver for a 64 bit environment and could not make a connection whatever I did. I finally downloaded and installed the 32bit version and am now able to connect to MySQL database on a remote server. As Excel is an also Office product I expect the problem/solution is the same.

Upvotes: 1

Code Animal
Code Animal

Reputation: 106

Quick Answer

Make sure you have installed the correct ODBC version (64-bit or 32-bit), matching the software. Try the following:

  • Replace Driver={MySQL ODBC 5.2w Driver} with Driver={MySQL ODBC 5.2 Unicode Driver}
  • Replace Driver={MySQL ODBC 5.2a Driver} with Driver={MySQL ODBC 5.2 ANSI Driver}

Long Answer

The Driver={driver name} part of an ODBC connection string is the driver name that is listed in the Data Sources (ODBC) dialog.

Because you are running in a 64-bit environment, what you do next depends on whether you are running Excel (or whatever the software, web app etc.) as 32-bit or 64-bit, and therefore make sure you've installed the appropriate MySQL ODBC driver version. This is probably the most common problem people run into with ODBC drivers on 64-bit systems.

For those running web servers and trying to get 32-bit ODBC drivers to work in their 32-bit web app, make sure you enable 32-bit applications on the application pool. Here is a forum post describing how to do this: http://forums.iis.net/post/2023543.aspx

64-bit

If the software is 64-bit then open the Run dialog box, and run %windir%\System32\odbcad32.exe. Click the Drivers tab, and find the MySQL ODBC driver listed, remember the name in the Name column, and use that in the connection string.
For 5.2.6 this will most likely be: MySQL ODBC 5.2 Unicode Driver

Where the driver name you tried before was 5.2w use the Unicode driver, where it was 5.2a use the ANSI driver.

32-bit

If the software is 32-bit then in the Run dialog box run %windir%\SysWOW64\odbcad32.exe to open the 32-bit Data Sources dialog, and then do the same as above.
On 64-bit machines using the 32-bit driver, it is recommended to start the connection string with Provider=MSDASQL;

Upvotes: 3

Related Questions