Using MySQL from Microsoft Access
You can use Microsoft Access to exchange data with a database on MySQL. This provides very convenient mechanisms for both populating your database initially, and for keeping it up to date.
If you have existing data in an Access database, an Excel spreadsheet, or a delimited text file, this can be read into Microsoft Access and exported to your database on the MySQL server.
Before you can use Access with MySQL, you must first have set up a Data Source for your MySQL database. Details of how to do this can be found at Using MySQL with ODBC.
The example described here uses Microsoft Access 97, which was provided on Staff WTS version 1. Working with later versions of Access is essentially the same, but may appear slightly differently on the screen, and some menu item names have changed in later versions of Access.
Exporting a database table
This example assumes you have already created a simple database with a single table such as the following. The table has a unique primary key student_id.
To export the table to MySQL, select the table name, and choose Save As/Export... from the File menu:
Make sure that To an External File or Database is selected, and click OK:
In the file browser window, choose ODBC Databases () from the Save as Type drop-down menu. You'll find this at the bottom of the list. Then click Export.
You then choose what table name you want to be used in the MySQL database. This defaults to the same name as the Access table. When you have chosen, click OK:
To select the Data Source to be used, you must first choose the Machine Data Source tab:
Now select the Data Source name that you have already created, and click OK.
Your table will now have been exported to your MySQL database, and may be used by other applications.
You may export data to MySQL as described above, and you can also import a MySQL table into an Access database. But it is far more convenient to "link" an Access table to a table on the MySQL server. Any change you make to a linked table is immediately reflected on the server, without the need to export the whole table again. In this example, we'll create a table linked to the MySQL table exported above.
With your Access database highlighted, choose Get External Data from the File menu, then select Link Tables...:
You will then be presented with a file browser, similar to that shown above for exporting a table. As before, choose ODBC Databases () from the drop-down menu at the bottom, and choose your DSN from the list of Machine Data Sources. You will then see a list of all the tables in your MySQL database. Select the table that you want to link, and click OK:
You must now select a unique field in your table. This is essential - if you do not do this, the table will be read-only within Access and you won't be able to make any changes:
In this example, the name of the table "Students" is already in use, so a new name is automatically given to the linked table. You can rename or delete a linked table without affecting the database on the MySQL server. The linked table appears in the list with a small world globe icon:
When you double-click the icon, it behaves just like a local table in the database file. You can amend any field or add new records. The changes are saved in the MySQL database as soon as you move to another record - no need to save or re-export the table.
Linked tables and auto-numbering
Access tables and MySQL tables do not behave in exactly the same way. For full details of compatibility issues you may encounter, see Connector/ODBC Application Specific Tips in the MySQL manual. In particular, Access auto-numbered fields are exported as simple number fields, so will not auto-number when linked back into Access.
The following work-around is suggested by the MySQL manual: After exporting your table to MySQL, use phpMyAdmin to make the field "auto-increment" - you do this using the "change" option on the page that shows the table's structure. You may have to make that field "unique" first. You then have to add a new field to the table. It doesn't matter where it goes (the last column is probably neatest) or what it's called, but it must be of type "timestamp". You can then make the link from Access to the table. When you add new rows to the table, leave the auto-increment field and timestamp field blank - MySQL fills them in for you.