Using MySQL with MS Access over the internet

With my recent work in MS Access*, I've learned some new things that I thought I'd share with you. This post has step-by-step instructions for using MS Access to connect to a MySQL database.

First of all, if you want to connect, you'll need to make sure you have the MyODBC driver installed on your machine.

To check to see if it's installed:

• Go to Start->Control Panel->Administrative Tools->Data Sources(ODBC).


• Click on the "Drivers" tab, and look to see if there's a MySQL ODBC driver installed. Mine was near the bottom.

To Install it if it's not:

• Go to http://dev.mysql.com/downloads/connector/odbc/3.51.html and
  download and install the windows driver.

Then, we'll need to set up the connection:

• Go to Data Sources(ODBC) again.


• Go to the "User DSN" tab and click "Add...".
  
• Choose the MySQL driver.
  
• Use a descriptive name for "Data Source Name". If you intend to use the smae MS Access database on multiple computers, you should make sure this name is the same on each.


• Use the hostname or IP address of the server for "Server", the username for "User" and the appropriate password.


• Click on the database dropbox, and select the database you're insterested in (it may take a moment).


• Go to the "Connect options" tab, and enter "set wait_timeout = 28800" in "Initial statement". That sets the timeout to 8 hours, which should be enough time to do your work. :-)


• Go to the "Advanced" tab.


• Under "Flags 1" check "Return matching rows"


• Click OK.

You may also find the information on MySQL's website ( http://www.mysql.com/ ) and http://www.ucl.ac.uk/is/mysql/access/index.html useful.

* I don't condone the use of MS Access (or any other MS program) when you're given an option, but sometimes you don't have one, ie, your client wants to use it.