4

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.
Tyson of the Northwest
  • 2,086
  • 2
  • 21
  • 34
  • Has the front end Access application been changed recently? Or are people starting to use a hitherto-untested section of the app? In Access, a common mistake is to edit a table in a bound form and then to attempt to update the same data using SQL. – David-W-Fenton Jul 14 '10 at 18:47
  • Other things to check: has the version of MySQL changed, the version of Access, the OS for the workstations, etc. Are the users accidentally launching more than one instance of the Access app (frequent cause of this problem among my less-than-stellar clients)? – David-W-Fenton Jul 14 '10 at 18:48
  • Last of all, do all of your data tables have a PK and a timestamp field in them and are both these fields included in the recordsource behind all your bound, editable forms? – David-W-Fenton Jul 14 '10 at 18:50
  • What about PKs and timestamps? – David-W-Fenton Jul 16 '10 at 00:57
  • Yes, there are PK and timestamp columns. – Tyson of the Northwest Jul 19 '10 at 21:25
  • And are the PK and timestamp columns in the recordsources of your forms? If so, I'm beginning to suspect something in the application, though I can't explain why it would have started occurring suddenly even though the app hasn't changed. Maybe the version of Jet/ACE has been changed by a Windows Update and that has changed the way Access interacts with ODBC. At this point, I'm just guessing. – David-W-Fenton Jul 20 '10 at 17:30
  • I think I may have found a solution. – Tyson of the Northwest Jul 20 '10 at 22:49
  • It needs to have a PK and Timestamp field and the timestamp field needs to default to CURRENT_TIMESTAMP and be previously set in the row before you edit it. If you want to write up a full Answer please do, otherwise I will write it up. – Tyson of the Northwest Jul 20 '10 at 22:55
  • It is really odd that it suddenly stopped working though. – Tyson of the Northwest Jul 20 '10 at 22:56
  • I assumed the timestamp field would be set to that, though I don't know the exact MySQL syntax used. – David-W-Fenton Jul 21 '10 at 18:34

2 Answers2

5

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

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • 1
    As well as a PK on each table you also need to make sure 'bit' fields have default values. Access cannot cope with Null Booleans. – Rob Sedgwick Aug 04 '15 at 15:15
  • 1
    Thank you so much David! saved my life adding a timestamp. Ms Access sure is a finicky database...not sure I would have ever solved the issue without your post! – daticon Nov 08 '17 at 12:32
  • 1
    Does Access automatically detect a timestamp column and use it in its ODBC update process? – WestAce Mar 08 '18 at 01:18
2

Make sure BIT columns have default values that are not NULL. Any records which have a BIT column set to NULL could get the Write Conflict error.

WestAce
  • 860
  • 3
  • 9
  • 23