Occasionally, you may receive the following write conflict error message:
Colin Riddington Mendip Data Systems 06/01/2019
To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks
When this error occurs, you can click one of the following three buttons:
• Save Record (often disabled as in the screenshot above)
• Copy To Clipboard
• Drop Changes
There are several reasons why this 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 uses multiple forms that have the same record source – modifying the form design should fix this issue
See this Microsoft support article for further information
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.
The above reasons weren’t 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.
Yet 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 CANNOT occur.
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 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 error will still occur in a form which includes the Boolean field in the record source even if its not on the form
Click any image to view a larger version
Difficulty level : Moderate
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 occurs using an update query, the query will fail and occasionally crash Access
Unfortunately, the write conflict message really does not make the reasons for the error clear
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