Recently I had to grant some users access to an existing Excel spreadsheet with a data source connection to MySQL over ODBC.
Their machines run 64-bit Windows 7. I installed the 64-bit version of MySQL Connector/ODBC, configured the appropriate ODBC DSN, and clicked the Test button in the Connector/ODBC configuration dialog box to verify a “Connection successful” result. After that we refreshed the Excel sheet (Data > Refresh All) and got the error:
[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
The googlebrain informed me that there are two copies of ODBC Data Source Administrator on 64-bit Windows installations. By default, “Control Panel > Administrative Tools > Data Connections (ODBC)” opens the 64-bit version. The trick is that MS Office requires a 32-bit ODBC connection which must be defined by the 32-bit copy of ODBC Data Source Administrator at “%windir%\sysWOW64\odbcad32.exe”. If you define only a 64-bit ODBC connection, you get the above error. I ran odbcad32.exe, added the 32-bit DSN, and was able to successfully refresh Excel : )
Note: To add a MySQL ODBC DSN, follow the instructions on the MySQL Connector/ODBC site–currently here for MySQL 5.1. If the Windows user does not have admin privileges, or if ODBC access should be limited to that one user, define a User DSN. If the Windows user has admin privs and the ODBC connection will be shared by multiple users, you can define a System DSN.