Caveat: this post is probably only of interest if you’re running into this particular MySQL ODBC Driver problem. Hopefully other people running into this issue will find this useful or please add a comment if you find a better way!
Today I had to get an Excel document that was loaded with ODBC database queries that connect to multiple MySQL databases working on a new machine. The problem I ran into is that if there are any MySQL permission issues such as a missing grant, authentication problems, etc… you’ll always get the following completely meaningless error message:
[MySQL][ODBC 3.51 Driver]Could not find driver {MySQL ODBC 3.51 Driver} in system information.
For the life of me I couldn’t even figure out how to determine what host/database/user/password a particular query was trying to use in Excel because if you click on Edit Query it would tell you it can’t find the driver. It’s hard to troubleshoot when you don’t even know which machine a particular query is trying to connect and it won’t even show you the SQL for the query if it can’t connect!
So to finally figure out what ODBC connections this Excel document was using I scp’d it over to a Unix machine and used strings:
strings document.xls | grep DRIVER | sort | uniq
which will give you a list something like this:
DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=dbname;SERVER=hostname;UID=user;PASSWORD=password;PORT=0;OPTION=0;STMT=; DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=dbname2;SERVER=hostname;UID=user;PASSWORD=password;PORT=0;OPTION=0;STMT=; DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=dbname3;SERVER=hostname;UID=user;PASSWORD=password;PORT=0;OPTION=0;STMT=;
That allowed me to see all of the database connections the Excel document was using so I could go in and fix the permission issues. If anyone knows an easier way, let me know.
So to finally figure out what ODBC connections this Excel document was using I scp’d it over to a Unix machine and used strings:
…
If anyone knows an easier way, let me know.
Uh, Cygwin?
Probably not what you meant. Heh.
Try using the ODBC configurator for a slightly more friendly way to set up a data source:
Control Panel -> Administrative Tools -> Data Sources (ODBC)
http://www.depts.ttu.edu/helpcentral/directions/ODBCSetup.php
Then you can select the configured Data Source from within Excel.
-f