1

If the user manually reverts a change to a field in a linked table in Access resulting in the record being unmodified overall, the 'Write Conflict' error message will be displayed when attempting to save the record. An example would be that the user makes a single change to a record by changing a field from 10 to 20, and then without saving the record re-enters the original value of 10.

Office Pro Plus 2016

MySQL 8.028

To reproduce this behaviour:

  1. Create a test database in MySQL
CREATE SCHEMA test;
USE test;
CREATE TABLE testtable (
  ID INT NOT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  col1 INT NULL DEFAULT NULL,
  col2 INT NULL DEFAULT NULL,
  PRIMARY KEY (ID))
ENGINE = InnoDB;
  1. Create an ODBC connection to the test database using a a user DSN and ANSI MySQL driver
  2. Create a blank access database and link to the testtable
  3. Open testtable and enter and save a couple of dummy rows. Change the value in one of the col fields. Without navigating away from the row or using undo, type the original value back into the field. Attempt to save the row.

I have deliberately included timestamp to demonstrate that this is not a fix.

I would appreciate advice from anyone who knows what causes this behaviour and how to prevent it.

Matt
  • 11
  • 1
  • Interesting. Sort of related: https://stackoverflow.com/questions/33224923/does-mysql-have-an-equivalent-of-sql-server-rowversion – Andre Feb 16 '22 at 16:21

3 Answers3

0

I found a workaround for this which is not ideal but it does work. I've added a boolean field to the SQL table called changeflag. In the OnDirty event of the Access form I call a simple procedure to change the value of this field. This way even if the user manually undoes a change that they made, there is still a change to the data somewhere else and the write conflict no longer occurs. The code is below.

Private Sub Form_Dirty(Cancel As Integer)
changeflag = Not changeflag
End Sub

user254181
  • 1
  • I had the situation with PostgreSQL - no booleans, but 2 date (timestamp) fields (since many solutions suggest using datetime fields to help with this) both were set with current_timestamp(), I had conflicts on update. When I updated the values of these 2 fields to only have dates (e.g. 22-12-20 00:00:00) rather than the seconds going to 5 decimal places, the conflict issue resolved. I then allowed updates to these fields to have hh:mm:ss.ff - 2 decimal places and the conflict issue is still resolved. Hope this helps someone! – Getafix Dec 22 '22 at 02:12
  • I have successfully used the 'changeflag' solution or work-around above to resolve my problem. – Dale Aug 08 '23 at 23:52
0

Turns out the solution is buried in the MYSQL ODBC documentation, the FOUND_ROWS connector/ODBC option needs to be set. In the GUI, it's under Cursor/Results tab of the Data source configuration: 'Return matched rows instead of affected rows'. After changing this setting, the conflicts no longer occur.

Matt
  • 11
  • 1
0

Yes, I have. The best workaround I could come with is to add a DATETIME field to the table. I called it rowversion. In the OnDirty event of the form add the following code:

rowversion=now

What that does is, whenever the user makes a change, the record will be updated with the current date and time. If they manually undo their change then the record is still updated and synced to the server.

user254181
  • 1