Access 2007 as front-end to MySQL on Windows 7

This article documents some issues with using Microsoft Office Access 2007 (32-bit) as a front-end to a MySQL 5.1 database on Windows 7 64-bit. It uses the MySQL 5.1 ODBC 32-bit driver as a bridge.

For an overview, go here: Using Connector/ODBC with Microsoft Access

Error: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Install the MySQL 32-bit ODBC driver, even if you have Windows 7 64-bit. You must do this when using Access 2007, because Access 2007 only exists as a 32-bit application. If you have Access 2010 64-bit, then you can probably use the 64-bit ODBC driver.

Typically, you would go to Control Panel > Administrative Tools > Data Sources (ODBC) and add a User DSN. However, if you do this, you will not see the MySQL ODBC 5.1 Driver listed because it is a 32-bit driver and you are using Windows 7 64-bit. Instead, to access the 32-bit ODBC data sources, run c:\Windows\syswow64\odbcad32.exe from the start menu. You can add the User DSN there and it will show up when in Access when configuring the DSN.

Access Crash when Linking MySQL Tables

After selecting tables to link and clicking OK, Access 2007 might crash unexpectedly. This may happen because the table name is too long! Shorten the table names in the source database and then the links will work fine.

This appears to be due to a Bug in the MySQL ODBC driver.