Can not edit older records from Access on MySQL database

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.

Ideas?
AMPLECOMPUTERSAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
<<Ideas?>>

  Make sure if the table has bit fields, that the default sets it to 0 and update any existing records with Nulls to 0.

  Failing that, add a timestamp field to the table, which should make it editable.

  Access (actually JET) has problems with real fields.  Due to floating point rounding errors, it often things a record has changed when it has not.

JimD.
0
 
AMPLECOMPUTERSAuthor Commented:
There are no bit fields in the table.

The last field (added around august of 2009) is a timestamp field.

Anything else I can try?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.>>

  Sounds like something gets updated when MySQL touches it.  Can you compare a record you haven't modified with one where you did?

  Got to be a problem with one of the fields.

JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 and compare both from Access and from MySQL Browser.

JimD.
0
 
AMPLECOMPUTERSAuthor Commented:
Found it. Any record that has a value in the timestamp field could be edited, any record without a valid timestamp generated the error. Using the Query Browser inserted a value into the timestamp field which Access could not do.

While you didn't actually fix the problem, you shoved my nose right into it, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.