Access varchar limit with SQL server - Write Conflict error

Question

I have a SQL Server 2008 database linked to an Access 2010 front end.

Some of the columns are varchar of varying lengths, from 50 to MAX. There is no default value and they can be null.

Recently, any of these columns longer than 255 started causing the write conflict error:

This record has been changed by another user since you started editing it. If you save the record, you will...

I can update other data, including varchar data, in the same row, with no problems and no errors.

This error occurs when I attempt to update the column directly from my table. It also occurs if I update the table programmatically via VBA or through a form.

It looks like this is because the column is NULL in SQL Server, but I've never seen this before with varchar columns. Is this a "feature" or a thing you just have to work around? It just started happening regardless, which is also confusing.

Answer 1

The leading cause of updatability problems in Office Access–linked tables is that Office Access is unable to verify whether data on the server matches with what was last retrieved by the dynaset being updated. If Office Access cannot perform this verification, it assumes that the server row has been modified or deleted by another user and it aborts the update.

There are several types of data that Office Access is unable to check reliably for matching values. These include large object types, such as text, ntext, image, and the varchar(max)(which is in your case), nvarchar(max), and varbinary(max) types.

Coming to your situation,i believe access forms may be bound to the data & produce a lot of locks on records and tables of SQL Server, those locks can prevent tables to be updated.So try to rectify Access forms which are bound to data and that are showing those updateability errors and make them work with unbound data, which allows you to read and update that unbound data to users without creating locks on the database server.