Write Conflict messages suddenly start happening in ODBC linked tables

Question

I have a mySQL database that tracks our projects and drives our website's display of their info. For ease of updating the database I have set up an access database that used an ODBC connection (MySQL ODBC 5.1) to edit the data. It has been working just fine for the past few months with no hiccups.

However, last night users(2 of 3) experienced Write Conflict errors. The users could only Copy the changes to the Clipboard or Drop the changes. So thinking there is something wrong with the Access database I created a new access database, linked the tables through the ODBC connection, and still the issue occurred. I also deleted and recreated the ODBC connection, to no effect.

So where do I go from here? What could have caused this issue to crop up now, not when I was setting this up months ago?

• There have been no changes to the database server, database or access database in the last week (+5 days).
• We have made sure that only one instance of Access is attempting to effect the database.
• All tables have a PK and a timestamp column.
• We are not using any forms, just using the Table interface.
• The server has not been updated, nor has the ODBC connection.
• We are using Access 2007
• Nothing is showing up in the server's error log when we try and update rows.

Answer 1

What does the server daemon (mysqld) think is going on? Look in your server logs, if you haven't already. I haven't done anything with MySQL in years, but from the docs online, think I would first look in the The General Query Log.

Answer 2

In general, all ODBC databases used from Access need to have PKs in all tables and timestamp fields in them that are updated each time the record is changed. Access uses this in bound forms for handling refreshes of the bound data and Jet uses them in in choosing how to tell the ODBC database what do update.

You may be able to get things to work with some tables without PK and timestamp, but I've found that it's best just to make sure that all your tables have them so you don't run into the problem (I never have any tables with no PK, of course).y