Editing Record issues in Access / SQL (Write Conflict)

Question

a problem has come up after a SQL DB I used was migrated to a new server. Now when trying to edit a record in Access (form or table), it says: WRITE CONFLICT: This record has been changed by another user since you started editing it...

Are there any non obvious reasons for this. There is noone else using the server, I've disabled any triggers on the Table. I've just found that it is something to do with NULLs as records that have none are ok, but some rows which have NULLs are not. Could it be to do with indexes? If it is relevant, I have recently started BULK uploading daily, rather than doing it one at a time using INSERT INTO from Access.

Solution 1

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 it's you, but Access doesn't know).

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

'This saves the form's 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.

Don't forget to refresh the table link in access after adding a timestamp column, otherwise Access won't see it. (Note: Microsoft's 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.

Solution 2

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 Olivier's suggestion:

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

And it solved the problem.

Solution 3

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.

Solution 4

I found the problem due to the conflict between Jet/Access boolean and SQL Server bit fields.

Described here under pitfall #4 https://blogs.office.com/2012/02/17/five-common-pitfalls-when-upgrading-access-to-sql-server/

I wrote an SQL script to alter all bit fields to NOT NULL and provide a default - zero in my case.

Just execute this in SQL Server Management Studio and paste the results into a fresh query window and run them - its hardly worth putting this in a cursor and executing it.

SELECT
    'UPDATE [' + o.name + '] SET [' + c.name + '] = ISNULL([' + c.name + '], 0);' + 
    'ALTER TABLE [' + o.name + '] ALTER COLUMN [' + c.name + '] BIT NOT NULL;' + 
    'ALTER TABLE [' + o.name + '] ADD  CONSTRAINT [DF_' + o.name + '_' + c.name + '] DEFAULT ((0)) FOR [' + c.name + ']'
FROM
    sys.columns c
INNER JOIN sys.objects o
ON  o.object_id = c.object_id
WHERE
    c.system_type_id = 104
    AND o.is_ms_shipped = 0;

Solution 5

This is a bug with Microsoft

To work around this problem, use one of the following methods:

• Update the form that is based on the multi-table view On the first occurrence of the error message that is mentioned in the "Symptoms" section, you must click either Copy to Clipboard or Drop Changes in the Write Conflict dialog box. To avoid the repeated occurrence of the error message that is mentioned in the "Symptoms"
  section, you must update the recordset in the form before you edit
  the same record again. Notes To update the form in Access 2003 or in Access 2002, click Refresh on the Records menu. To update the form in Access 2007, click Refresh All in the Records group on the Home tab.

• Use a main form with a linked subform To avoid the repeated occurrence of the error message that is mentioned in the "Symptoms" section, you can use a main form with a
  linked subform to enter data in the related tables. You can enter
  records in both tables from one location without using a form that is based on the multi-table view. To create a main form with a linked subform, follow these steps:

  Create a new form that is based on the related (child) table that is used in the multi-table view. Include the required fields on the form. Save the form, and then close the form. Create a new form that is based on the primary table that is used in the multi-table view. Include the required fields on the
  form. In the Database window, add the form that you saved in step 2 to the main form.

  This creates a subform. Set the Link Child Fields property and the Link Master Fields property of the subform to the name of the field or fields that are
  used to link the tables.

Methods from work around taken from microsoft support

View more solutions