Posted on Dec 28, 2008 by Stephen Owens Tutorial and Code category
Importing (and Linking) MySQL database tables from a website into Microsoft Access on your desktop. Explained in a very simple 9 step process, complete with pictures.
I recently worked on a project for where I had to link tables from a MySQL database on a website to an Access Database on a desktop computer. Searching the web I found a good amount of information about getting the Access Data into a MySQL database but nothing useful on going the other way around. So I thought I would share my process of importing and linking MySQL databases with Microsoft Access.
In the following steps I am using Access 2003. I have included pictures to help illustrate the steps.
Prepare the Website and Install MyODBC
1. Add your computers IP address to the acceptable remote MySQL connections.
- For example using cPanel look under the Databases header and click on the Remote MySQL icon.
- Enter your IP address and click the submit button.
- Note: If you don''t have cPanel on your web server, contact your site host and ask about setting up a Remote MySQL connection.
Figure 1: Remote MySQL icon in cPanel
2. Download MyODBC driver and install it.
Setup MyODBC Data Sources on your local Machine
3. Create a Data Source Name (DSN) for connecting to the remote database.
- For Windows XP, go to Start-> Control Panel->Administrative Tools->Data Sources (ODBC).
Figure 2: Data Sources Administrative Tool
- Add a new data source and then choose the "MySQL ODBC Driver".
4. The MySQL ODBC Connection dialog window will be open.
- Fill in your server connection information
- Include the port as part of the sitename.com (e.g. sitename.com:3306)
- Choose the database from the drop-down list and save the DSN.
- If no databases are available in the drop-down list then the server connection information was entered incorrectly.
Figure 3: MySQL ODBC Connection Dialog Window
Importing or Linking to the MySQL data from within Access
5. Open a new database in Access.
6. Then Click File -> Get External Data -> Import. (or Link Tables).
- Import will bring a copy of your table into Access.
- Link Tables will link you to your MySQL DB.
Figure 4: Import or Link Tables in Get External Data
7. A window will open asking you to browse for the file to get data from.
- Look at the bottom of the window for the Files of type drop down selection box.
- Under Files of type: select ODBC Databases()
Figure 5: ODBC Databases under Files of Type
8. The Select Data Source window will open.
- Choose the Machine Data Source Tab
- Pick the MyODBC connection you setup previously (in Step 4).
- Click the OK button.
Figure 6: Select Data Source
9. Now you will see your imported or linked table listed under the tables tab of Access.
- Linked tables have an arrow pointing at a world icon next to them.
- Imported tables have the normal table icon next to them.
- See Figure 7 for examples of each.
Figure 7: Imported and Linked Tables in Access from a MySQL Database
Your tables are now imported or linked to from Access. From this point all kinds of possibilities open up to you with Microsoft products. Perhaps you want to import data into Outlook, or use the data in Access to generate reports. What you do from here is up to you.