Access/SQL Write Conflict Error

Question

I am using Access 2007 as the frontend to a SQL table.  I can write a record to the table but I can not modify the record.  I get a Write Conflict error (This record has been changed by another user since you started editing it........ Copy to clipboard or drop changes).

I have received the error when I am the only person using the table.  What am I missing?

Thanks

Answer 1

On Wed, 28 Jul 2010 14:17:24 +0000, John P Spencer [MVP] wrote:

>Can you modify the table (or have it modified)?  If so try adding a field to the table(s) of the type TimeStamp.  The problem is that with certain versions of MS SQL Server, you can get the error you see unless you have the TimeStamp field.

Yes.  In later versions of SQL Server the TimeStamp data type is now

called RowVersion (a much more accurate name), although TimeStamp is

still supported as an alternate.

The write conflict can be caused by Bit fields with Null values, or by

floating point data types.  If you avoid both of those, you should be

fine without needing RowVersion.  For example, we don't use floating

point data types, and we use SmallInt instead of Bit for yes/no

fields.

If you want to use RowVersion, you merely need to add it to your table

and relink.  It does NOT need to be referenced in your Access queries

or forms.  ODBC uses it to check concurrency.

There is a slight downside to using RowVersion, as it eliminates the

possibility of partial-row concurrency checking.  I've included this

topic in my slide deck called "Best of Both Worlds" at our free J

Street Downloads page: http://ow.ly/M2WI.

Armen Stein

Microsoft Access MVP

www.JStreetTech.com

Answer 2

this doesn't work for me... i have no bit fields and my table has a timestamp.. any other suggestions?  i'm using sql server 2005 - acces 2010... this form worked using access 2007 but doens't now with access 2010..

thanks!

Answer 3

On Wed, 28 Jul 2010 14:17:24 +0000, John P Spencer [MVP] wrote:

>Can you modify the table (or have it modified)?  If so try adding a field to the table(s) of the type TimeStamp.  The problem is that with certain versions of MS SQL Server, you can get the error you see unless you have the TimeStamp field.

Yes.  In later versions of SQL Server the TimeStamp data type is now

called RowVersion (a much more accurate name), although TimeStamp is

still supported as an alternate.

The write conflict can be caused by Bit fields with Null values, or by

floating point data types.  If you avoid both of those, you should be

fine without needing RowVersion.  For example, we don't use floating

point data types, and we use SmallInt instead of Bit for yes/no

fields.

If you want to use RowVersion, you merely need to add it to your table

and relink.  It does NOT need to be referenced in your Access queries

or forms.  ODBC uses it to check concurrency.

There is a slight downside to using RowVersion, as it eliminates the

possibility of partial-row concurrency checking.  I've included this

topic in my slide deck called "Best of Both Worlds" at our free J

Street Downloads page: http://ow.ly/M2WI.

Armen Stein

Microsoft Access MVP

www.JStreetTech.com

Thanks a lot!!

This worked for me. Thanks

Answer 4

Thank you for your guidance. It worked for me too!