Solved Write conflict error (1 Viewer)

Kayleigh

Member
Local time
Today, 11:22
Joined
Sep 24, 2020
Messages
706
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

Super Moderator
Staff member
Local time
Today, 07:22
Joined
Feb 19, 2002
Messages
42,981
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

Member
Local time
Today, 11:22
Joined
Sep 24, 2020
Messages
706
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...
 

Attachments

  • writeConflict3.png
    writeConflict3.png
    12.8 KB · Views: 315
  • writeConflict2.png
    writeConflict2.png
    38.6 KB · Views: 345
  • writeConflict4.png
    writeConflict4.png
    31.9 KB · Views: 313
  • clientMarketing.png
    clientMarketing.png
    12.1 KB · Views: 319

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:22
Joined
Feb 19, 2002
Messages
42,981
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

MVP / VIP
Local time
Today, 11:22
Joined
Jan 14, 2017
Messages
18,186
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:

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
 

Users who are viewing this thread

Top Bottom