I was recently asked to see if I could devise an easy way to export from a MySQL table to an Access table. I found that if you set both databases up on ODBC, then use the Access menus File/Get External Data, you can have an entire MySQL table re-created in Access instantly. Then if you Link Tables (on the same menu), any changes you make to the MySQL table, including UPDATE and INSERT actions, are automatically passed to the Access table. Even if Access is not running when the MySQL modifications are made, when Access starts it will show the changes to the linked table. I am using MySQL 4.1 and Access 2003.
Thank you for the information.
I’m glad to hear that worked! For your application of viewing logged data inside Access, but using MySQL for the production system, I was thinking that we would have to log to both sources.
Your approach is much cleaner and easier.
What if you want to make the data go the other way, from Access to MySQL 5.0? Will the data go accross that way? I’m thinking, what about people who want to upgrade from Access to a real database server?
That link data feature should be able to work for changes made in Access. If you want to upgrade from Access to a real database server you would want to: export your Access schema and data, import to an SQL database, then get rid of Access altogether. This is a pretty cool option for the situation where front office personnel have existing Access applications and are trained in its use.