Write Conflict

[PJ]

Hi All,

I have an Access front end and SQL Server backend. When I update Comments
on the front end, I got the write conflict error, attached below. But when
I use MS Access Querry to enter the comments - it was saved. please help.
Thanks.

Write Conflict>
This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make the changes.

 

[Stefan Hoffmann]

hi,

I have an Access front end and SQL Server backend. When I update Comments
on the front end, I got the write conflict error, attached below. But when
I use MS Access Querry to enter the comments - it was saved.

Add TIMESTAMP field to your table and relink it.


mfG
--> stefan <--

 

[PJ]

Thanks Stefan,

but still not working - below is the setup i have.

fieldname: Timestamp
allow null: No
default value or binding: (getdate())

after update link in access i still get the same error. The value of
Timestamp is still Null when i modified the fields that allowed me to save.
only newly added have timestamp. Thanks.

 

[Paul Shapiro]

If you're using SQL stored procedures for data manipulation, include 'Set
NoCount On' at the beginning of the stored procedure. SQL Server normally
returns the count of number of records affected, even if it's zero, and
Access gets confused by that. If you want to see exactly what sql statements
Access is passing to SQL Server, use the SQL Server Profiler which can
capture every statement. Compare the statements that work with those that
fail.

 

[PJ]

So I tested some more - It only happens to some records. So I duplicated
one record that was having error - it worked fine - until I updated the
linking key in another table that it was having a relationship with to
populate the rest of the data - that's when it has the error if i tried to
update the data from the main table. Please help. Thanks.

 

[Paul Shapiro]

Not sure what to tell you about the presence/absence of related records. The
timestamp field however is another story. It's a terrible name for that
datatype. SQL Server has a data type called TimeStamp, which has nothing to
do with date/time. It should have been called rowVersionNumber. The
timestamp is a row version number which is unique for the entire database.
Each time you update a row in any table that has a timestamp field, the
timestamp is given the next larger number of the previous max for the whole
db.

The reason adding a timestamp field to the table can help is that Access
tries to be careful on your behalf. When you update a row using optimistic
concurrency, Access wants to verify that the row has not changed since you
last read it. If you have a timestamp field in the table, Access usually
uses this to perform the concurrency check. This usually works well, since
only a genuine update would change the timestamp value. If you don't have a
timestamp field in the table, Access usually sends a sql statement to SQL
Server that compares the new and old values of every field in the table.
It's this comparison that can sometimes fail, even without an intervening
update between your reading the row and writing the row.

For the timestamp field to help, you need to include it in your select
statement. It should not be included in the update statement since you
cannot update the value of a timestamp field. SQL Server maintains the value
as described above.

If your sql statement is selecting from more than one table, you can try
assigning the Unique Table property of the form. Access is less flexible
when the data is in SQL Server than with Access data. SQL Server data can
only update a single table at a time. Or try changing your form's
recordsource to be a single table, and use subforms for any related data.

I have found this to be a nasty issue. I have not seen a clear description
of how you can guarantee to avoid the problem. It's a bit of trial and
error, guided by the principals above. I seem to remember there are some MS
KB articles on the topic, so you could try searching there.

 

[Tony Toews [MVP]]

Not sure what to tell you about the presence/absence of related records. The
timestamp field however is another story. It's a terrible name for that
datatype. SQL Server has a data type called TimeStamp, which has nothing to
do with date/time. It should have been called rowVersionNumber.

FWIW I'm told that SQL Server documentation now states that the term
TimeStamp has been deprecated and you should use RowVersion. Or
something like that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Albert D. Kallal]

Thanks Stefan,

but still not working - below is the setup i have.

fieldname: Timestamp
allow null: No
default value or binding: (getdate())

Where did you get the "getdate()" default seeting from? Don't put that in
for the collum default.

Simply create a collom of data type timestamp. You don't need that default
value for binding...

Your table should have a primary key collum, AND ALSO add a collum of data
type timestamp. (but don't put in that getdate() as the default).

You don't mention what version of access nor sql server, but also check out
the null bits issue stated here:

ACC2000: Write Conflict Error When You Try to Update Records in a Linked SQL
Server Table
http://support.microsoft.com/?id=280730

 

[PJ]

Thanks everyone. All this time and I have the wrong datatype. Renamed to
RowVersion and datatype as timestamp instead of datetime with getdate().
Thanks again.