So what started off as a simple problem (adding a query into a Microsoft Excel spreadsheet) turned out to be a nightmare!
This problem seemed to affect both Office 2004 and 2008, I had previously that them working in OS-X 10.5, however I just got a new machine, which is 64bit capable and running 10.6.
Looking on the MySQL download page, there were no build for 10.6, only 10.5. So I experimented.
Here's what I tried
- MySQL ODBC Driver 5.1.6 x86 64 bit for 10.5
- MySQL ODBC Driver 5.1.6 x86 32 bit for 10.5
From the Apple ODBC Manager, I could create a DSN, I could connect to my MySQL database just fine. Going into Excel and then Data > Get External Data > New Database Query brought up the iODBC ODBC Manager. However when I tried to connect to my database, I got the following message.
[iODBC][Driver Manager] Specified driver could not be loaded
Office 2008 (if I recall is a x86 built product), so I tried with Office 2004 which I knew was PowerPC. Still the same message.
Now what I did next is very weird but works, I installed the following
- MySQL ODBC Driver 5.1.6 PowerPC 32 bit for 10.5
I had some issue trying to setup in the Apple ODBC Manager, so I hacked the .ini file in /Library/ODBC/odbc.ini. Here's a sample
[test]
Driver = /Develop/Developer/mysql-connector-odbc-5.1#377675/lib/libmyodbc5-5.1.6.so
PORT = 3306
DATABASE = testdb
PWD = notmyrealpassword
SERVER = db2.acme.com
UID = admin
So strike me down if this worked for Office 2004. For Office 2008 its even more weird, the PowerPC driver allows me to connect with Microsoft Query, but I need the x86 Driver to query from the spreadsheet. BUt you can't edit the query afterwards :-(
I just downgraded to Office 2004 for this, and will save the final conclusion for another day.
