An Access Front-End to MySQL - Preliminary Steps

(Page 2 of 4 )

I’ll give a quick overview of how to connect to your MySQL database, and I'll try not to repeat information that is readily available elsewhere. For instance, information on creating an ODBC connection is available on this site in the article, “Using MyODBC To Access Your MySQL Database Via ASP”.

Obviously, the first thing you will need is a MySQL database. My primary concerns are with online databases, but if you don’t have one, a local database will do for testing purposes. However, I should note that to connect to an online database you will need to be able to access it through the domain name and not, as you might from a script on the server, using “localhost”.

If you don’t already have the ODBC driver for MySQL installed, you can download it from the URL:

http://www.mysql.com/products/connector/odbc/.

At the time of writing, the name and version number is “MySQL ODBC Driver 3.51”. Once this is installed, check that a driver with this name shows up in the Drivers’ window of the ODBC administrator. You can see the article referenced above for information about how to create a system or a user DSN. You are now ready to use Access as a front-end to MySQL.

Connecting

Let’s quickly go through the steps necessary to connect to your MySQL database. Open Access and choose the “More Files” option from the dialogue box. Drop down the “Files of Type” list box, go to the bottom and choose “ODBC Databases”. Click on the tab “Machine Data Source” and choose the connection name you just created with the ODBC administrator. From here on it should be pretty straightforward although you may be asked to create primary keys for any tables without unique identifiers.

Following these steps will create a database in “My Documents” with the name “ODBC1.mdb” or a variant on that name. This database will then open showing all the tables with a globe icon to their left. This icon indicates that the tables are external, linked tables.

Double clicking a table name will open it in that familiar Access tabular format. Records can be added or deleted exactly as they would if the database was a native Access one.

Next: Limitations >>
 

More Microsoft Access Articles
More By Peter Lavin

Comments On: An Access Front-End to MySQL

· One other thing worth mentioning is that if you use pass thru queries you can use...    · Thanks for the article. I also found that an effective way to transfer a single...    ·     ·     · Question?? I am trying to add records to a Mysql table using access. The primary...    · RTFM ;-) *kidding*Have one more dummy column with TIMESTAMP as the data type,...    · I have Access set up as a front end to a MySQL database. I have a form in Access...    · I have Access set up as a front end to a MySQL database. I have a continuous...    · The problem was fixed. When I created the column with TIMESTAMP as the data...   >>> Post your comment now!