Last Updated 6 Jan 2019                         Difficulty Level: Moderate


Occasionally, you may see the following write conflict error message:




The following information is based on a Microsoft support article :

Access displays the Write Conflict dialog box when you and another person are attempting to write to the same record in the database at the same time.
The default locking strategy (called pessimistic locking) grants ownership of the lock to the first person to attempt to write to the record.
The other person may save the record before you do, but you can decide what happens because you own the lock.

There are two main reasons why a write conflict occurs:

a)   You have two objects (such as a table and form) open at the same time in the same Access database and both objects are attempting to write to the same record.
      In this case, the other person is you. The first object that attempts to write to the record owns the lock.

b)   The Access database is shared, and another person is attempting to write to the same record.
      In this case, the first person that attempts to write to the record owns the lock.

To resolve this conflict, click one of the three buttons:
1.   Save Record (often disabled as in the screenshot above)
      This overwrites the changes the other person made. Because you were the first to edit the record, you own the lock.
      The other person will most likely lose their changes. If possible, avoid using this option.

2.   Copy To Clipboard
      The entire record is copied to the clipboard with the changes you have made, and then the changes made by the other person are displayed in the record.
      You can accept their changes or decide to overwrite their changes based on what is saved in the clipboard.

3.   Drop Changes
      Even though you own the lock, you let the other person keep their saved changes.



As indicated above, there are several reasons why this error may occur including:
a)   Two or more users are trying to edit the same data at the same time – changing record locking may help alleviate this issue
b)   When you edit data that is used on multiple forms that have the same record source – modifying the form design should fix this issue

However, sometimes the issues are more obscure and can be tricky to pin down.

Several years ago, some of my users experienced this error periodically on a split database with a SQL Server backend.
None of the above reasons were applicable in this case and it occurred on several occasions with only one user logged in.
Trying to identify the cause proved difficult.

Testing indicated this occurred with certain records in selected tables whether done using a form or by directly editing the table.
However, other records in the same tables could be edited without any errors occurring.

It was eventually realised that the common factor was SQL tables with Boolean fields where no default value had been set.

In Access, boolean (yes/no) fields can only be true or false. Null values are NOT allowed.
However, in many other databases including SQL Server, NULL values are also possible UNLESS a default value is applied

In the screenshot below, the Active field has been set up in SQL Server to allow nulls and has no default value



Several records have been entered and the Active field set as true/false for some records (but not all).
For the purpose of this article, the Active field in records 6/7/8 has been deliberately left as null.



If the SQL table is then linked to an Access frontend, the same table looks like this:



The checkboxes look identical (empty) for both the false values (records 3/5) and the null values (records 6/7/8).

There is no problem editing records 1-5 but attempting to edit any field in records 6/7/8 causes the write conflict error UNLESS the Active field is first updated.



NOTE:
The write conflict error will still occur in a form which includes the Boolean field in the record source even if its not on the form



The issue causing the write conflict errors is that Access does not recognise the null values as valid, so is unable to process the changes being attempted.
If this error occurs when running an update query, the query will fail and occasionally crash Access

Unfortunately, the write conflict message really does not make clear the reasons for the error.
The solution in this case is very simple.

First change all existing null values on the Boolean field to false (or true) in Access or SQL Server.
However, this will not prevent issues with any new records.

Next in SQL Server, modify the table design by doing the following changes on the Boolean field:
a)  Set a default value e.g. 0
b)  Untick Allow Nulls



After relinking the table, all records will be editable in Access with no write conflict errors



Allen Browne describes a different issue related to Boolean fields when used in queries with an outer join. See Yes/No Bug

The lack of a null value causes a no current record error when there is no record on the right side of the join.
Work-rounds include the wrapping the Boolean field in the Int function or replacing the Boolean field with an Integer field with values -1 or 1 (true), 0 (false) or Null



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments/info to add to this article.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 6 Jan 2019

Return to Access Articles Page

Return to Top