1

Trying to implement Excel VBA: writing to mysql database.

Following code runs into a run-time error [MIcrosoft][ODBC Driver Manager] Data source name not found and no default driver specified

Dim cn As Object
Sub Connect()
Dim strCon as string
Set cn = CreateObject("ADODB.connection")

strCon = "DRIVER={MySQL ODBC 3.51 Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=dbname;" & _
    "USER=root;" & _
    "PASSWORD=mypass;" & _
    "Port=3306;" & _
    "Option=3"

cn.Open strCon

cn.Close

End Sub

checked connection parameters with the following php code

$mysqli = new mysqli('localhost','root','mypass','dbname');

and all works just fine.

I do have Microsoft ActiveX Data Objects 2.8 Library ticked in my VBA Project References.

Any help is welcomed.

Improve this question
6
  • 1
    These three links (here, here, and here) all describe an issue with the 32-bit and 64-bit versions. Please check if the issue is similar on your end. Commented Dec 24, 2013 at 1:50
  • @BK201. Indeed Im running xampp 32 in a win64 machine! I downloaded and installed the mysql 64 driver but that didnt help. Trying to follow instruction on the KB but this ODBC Data Source Administrator is a bit Greek to me. Any tip is welcomed! Commented Dec 24, 2013 at 9:08
  • Check the third link, under the Workaround header. Commented Dec 24, 2013 at 9:16
  • @BK201. I did. Still trying to figure out what 'create the ODBC data source' means and how to use this ODBC Data Source Administrator to do it. Commented Dec 24, 2013 at 9:39
  • 1
    Most probably an issue with the SQL versions. Sone swear by downgrading , some by other things. Anyway, kindly answer your own question and accept it. An additional reference for this issue is always a welcome hit on any relevant search. :) Commented Dec 26, 2013 at 14:40

2 Answers 2

Reset to default
1

For whatever it's worth at this point (since I just ran into it and it took me a day to figure it out):

You may have MySQL (or other Database/Application) in either 32 bit or 64 bit form. You may have Excel (or other application) in either 32 or 64 bit form. You may have the ODBC "connection" between the two in either 32 or 64 bit form.

Typically, the ODBC driver will be installed when you install the DB product, so it will be the same as the database.

Unfortunately, that's not important.

What IS important is that the ODBC driver match the application (e.g., Excel) properly.

If you have 32 bit Excel, and install 64 bit MySQL, that's fine, but Excel won't be able to connect to MySQL (except through the MySQL Connector, but that's not the goal here). You won't be able to write an Excel app to read, via ODBC, from the database.

The solution is simple. Download the MySQL ODBC driver for 32 bit (same place as MySQL), and install it. (It will tell you 'already installed' - because it will see the 64 bit version - and ask if you want to uninstall. Say Yes.)

Now, 32bit Excel can talk to 32bit ODBC driver, which can talk to 64bit MySQL, to exchange data.

Note: Far as I know, you can only install one type of ODBC driver (per DB App). So, if you are using the 64bit version, perhaps for some other app, you'll have to uninstall it (which breaks access to that App) to install the 32bit version so you can use Excel.

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

Comments

0

I have a 64 Win machine with 32bit Excel. I experimented with different MySQl ODBC drivers (5.1, 5.2, 5.3). The 64bit drivers did not work fro me, but the 32bit odbc driver did work. Somewhere in the Microsoft knowledgebase it mentioned that Excel does not work with the 64bit drivers.

I recommend using a more recent one than the one you mentioned in your post: http://dev.mysql.com/downloads/connector/odbc/5.2.html

This vba code worked for me 

Set oConn = New ADODB.Connection  
  With oConn  
    .ConnectionString = "Driver={MySQL ODBC 5.2 Unicode Driver};" & _  
          "Server=" & strServer & ";Port=3306;" & _  
          "Database=" & strDBName & ";" & _  
          "Uid=" & strUserID & ";" & _  
          "Pwd=" & strPasswd & ";Option=3;"   
     .open  
end with
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.