Solved Write conflict error

[Kayleigh]

I am having trouble updating any data in one table in MS Access which is linked to SQL server. This happens on form, view or basic table. It works fine to update on SQL Server direct. I have tried closing all open forms/tables and SSMS but still getting this error message.
Don't know what else to try. Any ideas?

 

[Pat Hartman]

Does the table have a primary key defined?

Is the form bound to a query rather than a table (it should be)? Is the query updateable?

Is the table using any data type that Access does not recognize such as BigInt or some of the new date data types?

 

[Kayleigh]

Hi before we address issue of form, can we work out why the table itself is not allowing edits in MS Access but works find in SSMS. It happens every time - even if I don't open any forms. It does allow adding records though.
I have attached some screenshots
Really puzzling as I've never come across something like this before...

 

[Kayleigh]

Found the issue here: https://stackoverflow.com/questions/13993301/editing-record-issues-in-access-sql-write-conflict

It required a timestamp column!

 

[Pat Hartman]

I use timestamps because It makes updates more efficient but I have never experienced the error you got when updating a table without one. The only time I've ever gotten that message is when there really was a conflict. In way too many cases, the conflict is with yourself rather than another user. People frequently run update queries that affect the current record in the bound form that is running the code.

 

[isladogs]

I rarely use the timestamp column & don't have the issue you describe

The cause is that you have two boolean fields in SQL Server set to allow nulls and probably with no default value.
Access cannot handle null values in boolean fields and throws a 'wobbly' .... AKA a write conflict error
See my article:

Write Conflict Errors

This article explores some of the causes of the 'dreaded' write conflict error and how to deal with those issues.

www.isladogs.co.uk

Modify the SQL table design by doing the following changes on the boolean field(s):
a) Set a default value e.g. 0
b) Untick Allow Nulls

 

[joehart]

if you add new bit column and you get the write conflict when you try go change a row you get write conflict error. TO FIX
write new update query set the column you just created to zero for all rows doesnt matter it is new column then problem solved you can now edit one row and change new bit column and not get write conflict error