Write Conflict error message on Access Linked SQL table

I have an access 2016 form which uses a linked SQL Server Table as its  Record Source, I have a Textbox control "booth_no" to add booth numbers. I can add the numbers but when I go to save it I get a Write Conflict error message and the only options I get is "Copy to Clipboard" and "Drop Changes", "Save Record" is grayed out. I selected selected the SQL table identity column which  is an INT as the Unique Record Identifier ([id] [int] IDENTITY(10001,1) NOT NULL,) when linking the table but it will not allow me to save the record. BTW all INT columns have a default value.
skull52IT director Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Add a timestamp column (turns on row versioning) and re-link the table.

Should be fine then.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52IT director Author Commented:
Ok, Add a date time data type column to the SQL table with the current date as the default, and that will be updated when the booth number text box is updated, correct?
skull52IT director Author Commented:
so I would Alter table
Add Version timestamp, would I use the Version column as Unique Record Identifier when relinking the table?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Ok, Add a date time data type column to the SQL table with the current date as the default, and that will be updated when the booth number text box is updated, correct?>>

  No, a timestamp column is not a timestamp despite the name.   Rather it turns on row versioning (which is what it's called in later releases).  There's nothing you need to do with it other than add it to the table and re-link.

<<Add Version timestamp, would I use the Version column as Unique Record Identifier when relinking the table?>>

  No, not as the unique ID.   Still use the identity column.  

  What row versioning does is let let the database engine in Access easily determine if a record has changed.   If a timestamp column is not in there, then it must do a compare on every field between what it has and the current value from SQL.

 The issue in doing that is with floats and bit fields.   Because of the way they work, Access will often think a record has changed when it has not.

Jim.
skull52IT director Author Commented:
Jim, So just Alter the table by adding the version column as timestamp, also I forgot to add that another application is adding to that able will that have a negative effect?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< will that have a negative effect?>>

 No.

Jim.
Dale FyeOwner, Developing Solutions LLCCommented:
no points please

you must ensure that every table you want to be able to insert to or edit from SQL Server has a primary key (having an identity column does not guarantee that it has a PK).  And I always add the timestamp column with:

Alter table tablename
ADD SSMA_Timestamp RowVersion
skull52IT director Author Commented:
Thanks Jim that worked perfectly.
skull52IT director Author Commented:
Thanks Dale
I will do that from now on.
I did
Alter table tablename
ADD versioning Timstamp
skull52IT director Author Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.