AMPLECOMPUTERS
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<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.
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.
and compare both from Access and from MySQL Browser.
JimD.
ASKER
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!
While you didn't actually fix the problem, you shoved my nose right into it, thanks!
ASKER
The last field (added around august of 2009) is a timestamp field.
Anything else I can try?