Access Write Lock Issue

Question

Hi,

I support an Access 2016 application that is used by several users. This application has been working fine for a long time, but today, we've run into an issue with one particular record in one particular table.

When any of our users go into this record and attempt to change a field, we get an error message with the heading 'Write Conflict'. The body of the message says "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. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes." The message box has three buttons - "Save Record", "Copy to Clipboard", and "Drop Changes". The "Save Record" button is grayed out and disabled.

I'm wondering if anyone here can tell me (1) if there is a way I can find out who has a lock on this record, and/or (2) if there is a way I can forcefully discard any changes that might have been made by another user, release the lock, and force my own changes to be saved.

Any advice will be greatly appreciated.

Thanks in advance,
Paul

Answer 1

While Scott and Imb_HB are right about the potential problem with an accdb, this particular error is most often associated with SQL Server linked tables, which is your situation.

The problem often results from attempting to update a record in VBA while it is simultaneously being edited in a bound form, or similar situations.

The solution often involves adding a Rowversion (formerly called Timestamp) field to the table(s) in the SQL Server database.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-ver16

You can also read more about using the Timestamp field in Access,

Here's a rather old discussion of this, which is still relevant, by Armen Stein. You can find a link to an excellent discussion of using SQL Server with Access in that post as well..

https://answers.microsoft.com/en-us/msoffice/forum/all/accesssql-write-conflict-error/8052e224-8dcc-48dd-acf3-7a93c346f970

Answer 2

Thank you George - that worked great! I am using SQL Server 2012. The rowversion datatype didn't seem to be an option, but I created a field of the older type 'timestamp', relinked the tables in my Access front end, and now I can edit the record. Or even better, the users of the application can!

If this turned out to be a one-time problem with just one record, I was ready to use update queries in SQL server to set the values as needed, but there are a lot of columns and I'm not that familiar with where all the fields are located on the form the users use, so it would have taken me a long time!

I appreciate your help! Have a great weekend!

Best regards,
Paul

Answer 3

I agree with Imb. When I've encountered this, it is often fixed with a Compact and Repair You can also check the laccdb file.

Answer 4

Hi,

This application has been working fine for a long time, but today, we've run into an issue with one particular record in one particular table.

Hi Paul,

Perhaps this record has been corrupted for some reason today.

You could try to Compact/Repair. If the record was corrupted, you will loose that record.

When you get a message about "an other user", it is not necessarily an other physical user, but it also can be a different recordset being edited. Has something changed in the code?

Imb.

Answer 5

Is the back end table in an accdb? Or is it in a different database, such as SQL Server?