Can not edit older records from Access on MySQL database
Posted on 2011-03-24
We have been using a MySQL database backend with an Access front end for years, it works fine until we try to edit an older record and then I get a write conflict from Access.
I can for example open the standard form, open a querry, or directly open the linked table from Access and edit anything entered in the last year and it works perfectly. If I try the exact same thing with something say a year and a half or older, I get a write conflict regardless of what colum I edit, or what I change the data to.
Now here is where things get really weird. I can open MySQL Query Browser (from the same workstation, not on the server hosting MySQL) and change the data by using an update statement, and that works fine. Now when I go into Access and do exactly what I did in the paragraph above, it works fine. It is like MySQL locks the record from Access until the record is edited and then unlocks it.
I am using linked tables through ODBC using the MySQL ODBC connector 5.1 with all default values. MySQL server is 5.0.67. The tables are MyISAM tables and I have run both the Extended check and repair options on the table in question.