The problem is as follows:
1. To use ODBC and Access to update a MySQL table, you need to have a TIMESTAMP type (any field name will do) in the record, and for it to be updated automaticalyl (which is the default behaviour for this data type in MySQL).
2. If you update a record in a MySQL database via ODBC, and the values of EVERY field is the same as the current record, the timestamp field is NOT updated. This seems to be intentional behaviour by MySQL as I found some blogs about MySQL actually doing the update in this case and that this was considered a bug.
3. If you edit a field but set it to the SAME value it had before, the client tries to save the record but the clever MySQL system decides that since the data has not actually changed, it does not change the TIMESTAMP field.
4. In MS-Access, this seems to cause Access to think that someone else has updated the record before you, and it shows the "Write Conflict" popup message. While this doesn't entirely make sense to me as I am not sure how it uses the TIMESTAMP field, it is definitely the reason for the problem. It also doesn't cause any lost data if you just "Drop Changes", since there was no change in the data anyway. It is just that the message is very annoying for users.
5. If you add a space to the end of a text box (MySQL VARCHAR), it strips the trailing space from the value, but treats the record as dirty and needing update. Also, if you have code which changes one field based on a change in another field, but the target field already had that value, the same thing occurs. It also happens if you type the same value over the top of an existing field, or if you change it and then change it back before saving. It happens in both data view of a table and bound forms.
WORK-AROUNDS
You *could* work around the problem by either checking whether any data is different and cancelling the record save event (which is a nasty solution).
My preferred method is to ensure there is always a change by creating a "LastUpdated" DATETIME field and setting it to Now() in the BeforeUpdate event. This means that you get around the problem as well as implementing a useful audit trail. Note that you have to ensure that any form based on a join of multiple tables updates the LastUpdated field on every table in the query. That could get a bit heavy in some cases, but it has to happen because any one of the tables could trigger the problem.
DUPLICATING THE PROBLEM
If someone wants to pick this up and look at it in details, it can be easily duplicated. Just create a table in MySQL 5.0 as follows:
CREATE TABLE `bugtable` (
`BugId` int(11) NOT NULL auto_increment,
`BugText` varchar(50) default NULL,
`BugNumber` int(11) default NULL,
`LastModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`BugId`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
Set up a data source using MySQL ODBC Connector 3.51.
Link to the table in MS-Access 2003 (SP 3) and open the linked table.
Create a few record, refresh manually to see the TIMESTAMP change. Overtype a field with the same value or add a space to the bugtext field and save the (lack of) change to see the problem.
I am using the base JET 4 engine for 2003 (4.0.8618.0), and "No Locks" option (i.e. optimistic locking), but it also occurs with "Lock Records" option (pessimistic locking).
It may occur with other versions of everything too - not sure how specific it is.
SOLUTIONS
I am happy to hear directly from anyone who can help with this or has a better work-around. It seems like many people have found the problem but been unable to isolate it. It can easily be confused with issues with the setup of the TIMESTAMP field, multiple TIMESTAMP fields, Date vs Date/Time conversions, Null boolean issues, etc. which are all separate problems. It is not specific to the example table above, so it's not a problem with the syntax of the CREATE statement which is a dump of the table done by Heidi SQL 3.0.
Start Free Trial
