You may have to make that field "unique" first. 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. In particular, Access auto-numbered fields are exported as simple number fields, so will not auto-number when linked back into Access. For full details of compatibility issues you may encounter, see Connector/ODBC Application Specific Tips in the MySQL manual. Linked tables and auto-numberingĪccess tables and MySQL tables do not behave in exactly the same way. 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. You can amend any field or add new records. When you double-click the icon, it behaves just like a local table in the database file. The table has a unique primary key student_id.
This example assumes you have already created a simple database with a single table such as the following. 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. The example described here uses Microsoft Access 97, which was provided on Staff WTS version 1.
Details of how to do this can be found at Using MySQL with ODBC. 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.īefore you can use Access with MySQL, you must first have set up a Data Source for your MySQL database. This provides very convenient mechanisms for both populating your database initially, and for keeping it up to date. You can use Microsoft Access to exchange data with a database on MySQL.