I find MS Access to be a convenient tool to interface with most relational database engines (most notably, MySQL and MSSQL). I am much more productive navigating through linked tables in Access and writing queries against them rather than using custom tools such as PHPMyAdmin or SSMS.
With Windows XP, this workflow as trivial. However more recent Windows 7 systems involving a mix of 64 bit h/w and 32 bit s/w coupled with user account restrictions have made RDBMS access via ODBC a little challenging.
I have documented below the method to get MyODBC (ODBC driver for MySQL) working with MS Access 2007 in a 64 bit windows install. Of course, the same process can be used to link to any other odbc dsn after installing appropriate drivers.
- MOST office installations are 32 bit apps. (Although 64 bit office is available, I have yet to see a production install of it- in fact, even MS strongly discourages its use). Windows 7 uses a WOW64 (Windows 32 On Windows 64) subsystem to ensure 32 bit apps work seamlessly on 64 bit machines.
- Windows 7 uses much tighter user account restrictions. Because ODBC creation accesses the windows registry, it is a privileged operation.I assume that you are working using a user account AND have an admin uid/password should you need privilege escalation.
- A combination of 1 and 2 above make creating an ODBC DSN from MS Access on win7 a slightly convoluted process(compared to xp).
- Download and Install the 32 bit driver (MSI) for MyODBC from the following location: http://dev.mysql.com/downloads/connector/odbc/ (NOTE: There is a 64 bit MyODBC driver available for download.. but it is only to be used with 64 bit office apps. So, please download only the 32 bit version!)
- Navigate to C:\Windows\SysWOW64 and locate the file named odbcad32.exe. Create a shortcut to this file on your desktop so you will have a handy reference available (This is the WOW equivalent of the Data Sources(ODBC) applet found under ControlPanel->System and Security->Administrative Tools) You will launch this shortcut in order to create your (SYSTEM) DSN’s and NOT the applet in your control panel. This operation requires privilege escalation so you will be prompted for your admin uid and password in order to create your dsn.
- Once you have created your system DSN’s in step 2 above, you are ready to use them from within MS Access as before.