sql server – Editing Record issues in Access / SQL (Write Conflict)

sql server – Editing Record issues in Access / SQL (Write Conflict)

Possible problems:

1 Concurrent edits

A reason might be that the record in question has been opened in a form that you are editing. If you change the record programmatically during your editing session and then try to close the form (and thus try to save the record), access says that the record has been changed by someone else (of course its you, but Access doesnt know).

Save the form before changing the record programmatically.
In the form:

This saves the forms current record
Me.Dirty = False

Now, make changes to the record programmatically

2 Missing primary key or timestamp

Make sure the SQL-Server table has a primary key as well as a timestamp column.

The timestamp column helps Access to determine if the record has been edited since it was last selected. Access does this by inspecting all fields, if no timestamp is available. Maybe this does not work well with null entries if there is no timestamp column (see 3 Null bits issue).

The timestamp actually stores a row version number and not a time.

Dont forget to refresh the table link in access after adding a timestamp column, otherwise Access wont see it. (Note: Microsofts Upsizing Wizard creates timestamp columns when converting Access tables to SQL-Server tables.)

3 Null bits issue

According to @AlbertD.Kallal this could be a null bits issue described here: KB280730 (last snapshot on WayBackMachine, the original article was deleted). If you are using bit fields, set their default value to 0 and replace any NULLs entered before by 0. I usually use a BIT DEFAULT 0 NOT NULL for Boolean fields as it most closely matches the idea of a Boolean.

The KB article says to use an *.adp instead of a *.mdb; however, Microsoft discontinued the support for Access Data Projects (ADP) in Access 2013.

Had this problem, same as the original poster. Even on edit directly using no form. The problem is on bit fields, If your field is Null, it converts Null to 0 when you access the record, then you make changes which this time is the 2nd change. So the 2 changes conflicts. I followed Oliviers suggestion:

Make sure the table has a primary key as well as a timestamp column.

And it solved the problem.

sql server – Editing Record issues in Access / SQL (Write Conflict)

I have seen a similar situation with MS Access 2003 (and prior) when linked to MS SQL Sever 2000 (and prior). In my case I found that the issue to be the bit fields in MS SQL Server database tables – bit fields do not allow null values. When I would add a record to a table linked via the MS Access 2003 the database window an error would be returned unless I specifically set the bit field to True or False. To remedy, I changed any MS SQL Server datatables so that any bit field defaulted to either 0 value or 1. Once I did that I was able to add/edit data to the linked table via MS Access.

Leave a Reply

Your email address will not be published.