It is an incredible feature of MS Access that it is so easy to connect to different databases and use as a Management Studio. Having a unified platform across all the different backends is very very useful. Setting up those connections is not always straight forward and as ever involves configuration – something which often evades all but the most accurate of intelligent guesswork. This is set out for MS Access 2003 but I would expect this to work on all versions of MS Access.
Use the architect version of the MySQL driver that relates to the version of MS Access that you are using. In this case MS Access 03 is 2003 so I used this.
MySQL ODBC drivers at August 2017
Install as per normal driver.
Then open up ODBC Data Source Administrator – I have two options here 32 bit and 64 bit – its not clear if there is a difference but I have been choosing the 32 bit version – Navigate to file DSN and then hit Add…
You will be presented with a Create New Data Source window – navigate down to the MySQL and here choose unicode – ANSI and Unicode are two character encodings that were historically in wide use. Ansi is very old and is used by older operating systems like Windows 98. Unicode is newer which has a vast character set and is used by Office 2003 and upwards. UTF-8 is even newer.
Select then hit the next button.
You will be presented with a Create New Data Source dialog in which you can hit the Browse button and create the name of your file dsn. Here I have already created three – 2 are SQL Azure dsns and the third is a link to my inspirunner database. A hosted account.
You should then be shown the MySQL Connector / ODBC
You absolutely must know the name of your server – the port it is listening on and the user password – once these are filled in selecting the downard arrow should allow you to select the database.
Hit test and you should see success and then hit OK.
Now simply go into MS Access 2003 as per usual right click in the tables window and select the file dsn from the location you stored it in – you should be given all the tables from which to select