Error: "This record has been changed by another user..." when updating fields with same value

[Jean Choquette]

Hi everyone,


I'm using Access 2000 as front-end to MySQL 5.0.22 tables. When using form with bound controls or linked MySQL tables in datasheet view, I have a problem when updating records after editing them. If ALL the original values are still there for each field of the record that is updated, the error "This record has been changed by another user..." occurs and I have the choice to copy to clipboard of cancel.

I have one TimeStamp field on all my tables. To reproduce this error, I can open any linked MySQL table in Access, modify one field (to get into "edit mode" with the pencil to the left) and put back the original value before changing record (which forces an update). If the new value in the field(s) is a different value than the original, the timestamp updates automatically, but if it's the same value, the error occurs. For example: I take a string field with value "Hello", I remove the "o" to get "Hell" and I put back the "o" right away before changing record.

Since this behavior is happening on every table, I'm wondering if a quick fix can resolve this or if I will have to force a field update on the "before update" event of each form to make sure that at least one field has a new value when update occurs?

The timestamp field is to resolve the #deleted error that many people talk about in this forum (and that I experienced myself), and I also activated the following options in MyODBC:
Don't optimize column width
return matching rows
change bigint columns to int

Does anybody knows how to handle this situation?

Thanks,

Jean



Edited 1 time(s). Last edit at 08/07/2006 11:10AM by Jean Choquette.