Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Updated 06/01/2019

Write Conflict Errors

Return to Home Page

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

 

 

Colin Riddington      Mendip Data Systems        06/01/2019

Return to Top
* Required

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

Return to Access Articles

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

WriteConflictError2 SQLTableDesign SQLTable1 AccessLinkedTable WriteConflictError WriteConflictForm

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

SQLTable2

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