Connecting Access to a MySql Database

Back in 2004 when I started my first online business, I needed a way of tracking customers and all of their information. I decided to use MySql databases to store that information so that I could access the data from any computer that had internet access. I designed a website (php) as my front end to access the data. I called it the Matrix. lol. I just liked how it sounded.

I programmed the matrix site to have a pretty in depth array of features, but for reporting purposes I’ve always liked working with Microsoft Access because… well, it’s just really simple to use. So I used Microsoft Access for most of the reporting and data analysis. Guess what I called it? Yep, The “Offline” Matrix. lol. Anyway, To get it to work I needed Microsoft Access to connect to my MySQL databases and import the data so it can be used locally on my PC.

ODBC and Microsoft Access

You can use ODBC to connect Microsoft Access to your MySql database. The Connector/ODBC can be downloaded from MySql. I actually just updated mine today with version 5.1.6. Once you’ve downloaded and installed it, Open up Microsoft Access… Select File | Get External Data | Link Tables. In the dialog box, change file type to ODBC databases. This will trigger another dialog box; Click the ‘Machine Data Source’ tab and then the ‘New’ button. On Windows Vista, I get a DSN error, but it still allows me to continue once I click OK. Another dialog is opened; select ‘User Data Source’ and press ‘Next’. From the list, select ‘MySQL ODBC 5.1.6 ODBC Driver’. Press ‘Next’ and ‘Finish’.

Now you should be at the Data Source Configuration dialog box. Fill out the connection parameters.
Data Source Name: Enter whatever you want to call the connection.
Server: Enter the server where your MySql databases are hosted. My databases are hosted with LunarPages and so I would sign into my lunarPages account and copy the IP address that is displayed on my cPanel home page.
User and Password: Enter the user name and password that you set up for your MySql database (Don’t forget to include the id that preceeds the user name and password). Once you enter valid information, the database drop down box will be populated. Select the database you want to connect to and press ‘test’.

Click on ‘Details’ in the lower left corner and under the ‘Flag 1′ tab, make sure to check the following two boxes:
1. Return matched rows instead of affected rows.
2. Enable auto re-connect.
Press OK and it will populate MS Access with links to your MySql tables. That’s it.

HeidiSql

I’ve never really liked using phpMyAdmin to administrate my MySql databases, so originally I purchased a program called MySQL Front, but a few years later it was discontinued. I think HeidiSql is it’s replacement, but i’m not sure. Either way, HeidiSql was free and worked better than the other program, so that’s what I used (and still use) to administrate mySql databases.