MS Access "write conflict" error after refreshing linked tables, ONLY occurring in run-time installation of MS Access

Question

I am experiencing a very odd issue, getting "write conflict" errors after some simple updates, which ONLY occur in a runtime version of MS Access. Note that in this application Access is only a used as a Front End, the back end is SQL Server, with connection string of each table being: Description=Pilot;DRIVER=ODBC Driver 17 for SQL Server;SERVER=sql01;Trusted_Connection=Yes;APP=Microsoft Office;DATABASE=JCDC_pilot_DEV;

We have had this application working for years, and maintain both a prod and dev SQL back-end databases. MS Access is hosted through separate RDP environments, one of which (for external users), only has run-time version of MS Access, which automatically opens accdb files as if they were accde files (the other has full version of MS Access)

Recently, we added some additional validation logic, testing it in Dev, and when we were ready to update the Dev FE to a Prod FE by updating the linked table manager to point to "Prod" database, all works and tests fine for Dev (FE and SQL DB), as does the Prod (FE and SQL database) when being run in RDP environment with full version of MS Access - notably, it even works when saved as an accde (runtime version). It is ONLY when I re-link the tables to Production SQL back end that I get "write conflict" errors ONLY in the RDP environment with run-time version of Access. Furthermore, the Access FE with Dev SQL DB works fine in the RDP environment with run-time version of Access.

In troubleshooting, it seemed evident that it WAS NOT the added Access VBA code that was the culprit of the issue, but rather the linked table refresh. Even with no code changes (to latest working prod Access FE), it yields the "write conflict" error to any edit made in the Access FE (in run-time environment ONLY).

I've tried a number of workarounds without success, and keep getting the same error in the specific circumstances described above. The only changes made to the structure was adding a boolean flag (to one of three primary data tables), and timestamps to the three primary data tables. But what is s confusing is that the Dev version DOES NOT throw the "write conflict" error (even in the RDP environment with runtime version of Access)

Does anyone have suggestions, or some additional ways to troubleshoot the cause of the specifics of the "write conflict" cause?

Any help would be greatly appreciated

Answer 1

For the sake of this answer I'm going to assume you have primary keys defined on the tables, but if you don't, you need to.

By timestamps do you mean they are columns with a rowversion (or timestamp) data type?

By definition the value of a column with this type will change any time an update touches this row, even if no values are actually changed:

create table #t (c char, rv rowversion);
insert #t(c) select 'a';
select * from #t;
update #t set c = 'a' where c = 'a';
select * from #t;

On my system this produces the following output:

c	rv
a	0x0000000000022D36

c	rv
a	0x0000000000022D37

If a rowversion column exists, MS Access will use it to decide whether the row has changed, following a optimistic concurrency pattern. If user A loads the row with a rowversion value of 0x01, then user A later goes to save that row, but the rowversion column value is now 0x02 because of an intervening update, MS Access will pop up a write conflict error.

Before you had the rowversion column, MS Access would make this decision by inspecting the actual value of all columns. So in a scenario like the one in my little sample code, but without the rowversion column, MS access would not have warned about a write conflict, because even though I actually did run an update statement against the row, none of the row data had actually changed.

So the question is obviously going to be "OK, so what's doing the update that is causing the rowversion value to change?" Unfortunately that's not something we're going to be able to answer. You will have to inspect your software. But as to something that might point you in the right direction, be aware that if you are using the "table editor" view in MS access (it's been a long time since I used MS access so I don't know it's technical name), and you have users "tabbing through cell values", MS access may be treating this as an update, and sending the "new" (but unchanged!) values to the the linked table as an update, even though the user has changed no user values. You can determine whether this is actually happening using extended events or good ol' SQL Server Profiler (deprecated, but easier to use in my opinion).

Now, as to why you see it in prod, but not dev, I can only make a guess, but it's a reasonable one: In prod you have a lot more users touching the data, or a lot more often, than in dev. So these rowversion values are changing much more frequently than in dev, so you see the write conflict errors in prod, but not dev.

Also, if you are "relinking" the tables from the same Access runtime to a different database, the underlying values will change. I don't know how Access would treat that. It seems odd to me to do this "relinking from dev to prod" in the first place. I would think you would have a completely separate copy of your application stack for dev, not a single interface layer that you point to different SQL Server environments!

There is another long shot possibility to do with bit columns, described in this answer, but it is very old information, and the KB on the wayback machine implies that it only applies to the old access .mdb files.