Write Conflict in Access Form but not in SQL Server

[eggy168]

Hi,

I found a weird situation in Access. I have an Access FE which is using for update/delete data in the SQL BE by users. Users have the authorities for update/delete the data in the table.

It doesn't have this problem before, but it happens all of sudden. It has the 3 columns are Keys and couple more columns,
1) ID (int), Identity Yes, Identity Seed ~1 and Identity Increment ~1
2) Dept (varchar, 3)
3) LineItem (varchar, 8)
4) Equal (bit)
5) Earmark (money, 8)

When I try to change the data in the Earmark column, there is an error message in Access, Write Conflict ~ This record has been changed by another user since you started editing it. If you save the record, you will overwirte the changes the other user made.

I am sure no one in the database or in the table or in the record. I just want to know why it has this message? However, I can delete or update the information in the SQL Server.

Thanks for the help.

 

[PHV]

Either add a TimeStamp field or don't allow Null for the Bit field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

I agree with PHV that it is the bit field (equal) that is causing the problem and as indicated the timestamp field on the sql server table should fix the problem.

 

[eggy168]

I have other fields in the table, too..but since PHV and cmmrfrds both agree on the timestamp field. I will try it.
Thanks.

 

[cmmrfrds]

One more thing, include the timestamp field on your query so that ODBC, DAO, or ADO will know that it is a field that is gauranteed to be unique. If these libraries know there is a field gauranteed to be unique then they won't concatenate all the other fields to establish uniqueness which is when the bit field causes the problem.

 

[cmmrfrds]

Ignore what I said about including the timestamp field in the query, sql server does return it even through you don't ask for it. I checked some documentation that indicates sql server will return the timestamp field as a hidden field in the recordset so it is handled behind the scenes.

 

[cmmrfrds]

Here is some documentation on ADO updating.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00e1.asp

 

[eggy168]

Thank you very much, cmmrfrds.
It works no problem after I added the Timestamp field so far.