MS Access ODBC with PostgreSQL Table Write Conflict

2
votes

So I have to transfer the back end of an Access Database to Postgres recently. After linking the tables to the forms and updating the VBA coding, I tried to create a new record with the primary key field. I verified that this field was created successfully through the back end, but when I try to insert new data into the fields and upon saving the data Access always give me this error.

"Write Conflict:this record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made." And I am only given two options: Copy to Clipboard and Drop Changes.

The weirdest part is that if I try to open an existing record and modify the fields, I can save successfully. The write conflict error only pops up on the new records that I added.

I tried do some research online, and I had add time stamps to the table and all tables have PKeys. Can anyone explain the reason behind this? Very much appreciated!

1
vbapostgresqlms-access
Can I assume you are using a Postgres ODBC driver? If the Access tables use AutoNumber fields, do the correspond linked tables reflect them? - ron tornambe
In Access can you open one of the linked tables in Datasheet mode and manually add a new record there? - Gord Thompson
@rontornambe: Yes, I am using an ODBC driver and and i used the serial type to reflect auto number fields. - SuSanD
@GordThompson:Good point. I can enter in the primary key and the auto number can get automatically filled in. But when I try to enter any other fields, it gives me the write conflict again. - SuSanD
So a workaround would be: Make a query of the odbc-linked Table, without the timestamps columns (created_at, updates_at), you will not have any conflicts anymore. - Paul Verschoor

1 Answers

4
votes

I know that this question is old, but problem still exists (tested on MS Access 2013) and some people might be looking for solution.

According to PostgreSQL FAQ this is caused by slight differences between Access and Postgre in 2 specific cases. While updating a record, access adds some conditions to ensure that record wasn't changed by anybody. If any of them fail database returns that no records were updated, which is interpreted as they were modified in the meantime.

Empty strings

Access doesn't know the difference between empty string ('') and NULL while postgre does. If you leave empty string in a field, Access add ... AND column_name IS NULL" which fails in database. There's only workaround currently - make sure that all empty strings are stored as NULL.

Timestamps

PostgreSQL timestamp has microsecond precision while Access supports only milliseconds. In this case Access rounds the excess, so for example 2016-02-22 12:34:56.788952 is converted to 2016-02-22 12:34:56.789 which later fails on comparison. This can be fixed by lowering precision of the timestamp in database. Change timestamp type to timestamp(3) (for millisecond precision) or timestamp(0) (for second precision).

I hope it helped.