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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so I would Alter table
Add Version timestamp, would I use the Version column as Unique Record Identifier when relinking the table?
Add Version timestamp, would I use the Version column as Unique Record Identifier when relinking the table?
<<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.
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.
ASKER
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?
<< will that have a negative effect?>>
No.
Jim.
No.
Jim.
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
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
ASKER
Thanks Jim that worked perfectly.
ASKER
Thanks Dale
I will do that from now on.
I did
Alter table tablename
ADD versioning Timstamp
I will do that from now on.
I did
Alter table tablename
ADD versioning Timstamp
ASKER
Thanks
ASKER