Using MS-Access with MySQL

In a project I was working on recently, I moved the backend database from Microsoft Access to MySQL. It worked for the most part, but every so often I would get Write Conflict errors on certain rows of a table.

write conflict
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.

After checking various web pages for a cure, I came up with a list of things to remember when migrating MSAccess to MySQL:

• If you are using Access 2000, you should get and install the newest (version 2.6 or higher) Microsoft MDAC (Microsoft Data Access Components) from http://www.microsoft.com/data/. This fixes a bug in Access that when you export data to MySQL, the table and column names aren’t specified. Another way to work around this bug is to upgrade to MyODBC 2.50.33 and MySQL 3.23.x, which together provide a workaround for the problem. You should also get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5) which can be found at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114.
  This fixes some cases where columns are marked as #DELETED# in Access.
  Note: If you are using MySQL 3.22, you must to apply the MDAC patch and use MyODBC 2.50.32 or 2.50.34 and up to work around this problem.
• For all versions of Access, you should enable the MyODBC Return matching rows option. For Access 2.0, you should additionally enable the Simulate ODBC 1.0 option.
• You should have a timestamp in all tables that you want to be able to update. For maximum portability, don’t use a length specification in the column declaration. That is, use TIMESTAMP, not TIMESTAMP(<var>n</var>), n < 14.
• You should have a primary key in the table. If not, new or updated rows may show up as #DELETED#.
• Use only DOUBLE float fields. Access fails when comparing with single floats. The symptom usually is that new or updated rows may show up as #DELETED# or that you can’t find or update rows.
• If you are using MyODBC to link to a table that has a BIGINT column, the results are displayed as #DELETED. The work around solution is:
  • Have one more dummy column with TIMESTAMP as the data type.
  • Select the Change BIGINT columns to INT option in the connection dialog in ODBC DSN Administrator.
  • Delete the table link from Access and re-create it.

  Old records still display as #DELETED#, but newly added/updated records are displayed properly.

• If you still get the error Another user has changed your data after adding a TIMESTAMP column, the following trick may help you:
  Don’t use a table data sheet view. Instead, create a form with the fields you want, and use that form data sheet view. You should set the DefaultValue property for the TIMESTAMP column to NOW(). It may be a good idea to hide the TIMESTAMP column from view so your users are not confused.
• In some cases, Access may generate illegal SQL statements that MySQL can’t understand. You can fix this by selecting "Query|SQLSpecific|Pass-Through" from the Access menu.
• On NT, Access reports BLOB columns as OLE OBJECTS.
  If you want to have MEMO columns instead, you should change BLOB columns to TEXT with ALTER TABLE.
• Access can’t always handle DATE columns properly. If you have a problem with these, change the columns to DATETIME.
• If you have in Access a column defined as BYTE, Access tries to export this as TINYINT instead of TINYINT UNSIGNED.
  This gives you problems if you have values larger than 127 in the column.