For some time I was using Access to store and manage some simple data for a web site. Unfortunately, due to a change in hosting provider I was not able to continue using the Access database and had to migrate it to MySQL. One thing I wanted was to keep the Access forms I’d created for updating the data in the database and to create an external link in Access to the MySQL database through MyODBC. Creating the external link wasn’t a problem but there were a few gotchas that I discovered along the way to getting the forms working correctly.
There are many available tools to convert from MS Access .MDB files to a MySQL database. A couple are listed here:
MySQL.org – MySQL Migration Toolkit
I settled on the tool from MySQL which did the job perfectly. It was a simple database so your mileage may vary when converting. MySQL have a great tutorial on using the migration toolkit.
Once the data is in a MySQL database you can use a number of tools to see it. I tend to use the free edition of DBTools’ DBManager Professional which is much like SQL Server Enterprise Manager for those that are familiar with that particular tool. DBManager Pro can be downloaded from this site.
At first everything seemed to be OK. I was able to link the database tables into the Access front-end using MyODBC and I could skip records forwards and backwards in the interface I’d created. It wasn’t until I tried to update a field that I got my first error.
“This record has been changed by another user since you started editing it.”
After some searching I discovered that I needed to create a new (column) field in the MySQL table and assign it a data type of “timestamp”. The screenshot below shows this being done in DBManager Pro.
I then started to get the error “Reserved error (-7776); there is no message for this error”. Inserting and updating NEW records was working fine but I could not update records that had existed before the migration from Access. This was resolved by updating the “timestamp” column on each record with a date.
UPDATE table1 SET timestamp = “2005-01-01 01:00”
After that I experienced no more problems updating new or old records.