Possible reasons for Access messages “Data Being Updated by Another User”

I was asked to investigate, why in Access 2000 linked table(to SQL server database) any change in data was refused with message returning “Data Being Updated by Another User”.

The actual message could be misleading, because it returns if Update SQL returns 0 records back.
Access creates dynamic SQL passing in WHERE clause current values of fields.
However if column was created as BIT NULL and actually has NULL value, access sends in WHERE columnName=0 , which causes mismatch.
The problem happened in MS Access 2000 and MS Access XP.

Another issue in  Access 2000(not a problem in XP) is if your DateTime column stores seconds(or seconds with milliseconds). The access passes back in WHERE value in format ‘Dec 8 2006 5:59PM’ truncating seconds and milliseconds, which causes mismatch.

Possible  workarounds:
1. Run SQL
 update Table  set BitNullableColumn=0 where BitNullableColumn is null


update Table  set DateTimeColumn=Convert(nvarchar(30) ,DateTimeColumn, 0)


2. Create your BIT columns as NOT NULL.



3. Do not use old versions of Access( hope that new versions of Access handle the issue correctly)


4. Do not use  Access at all.


5. Update: I was adviced that the way to solve this problem is to add a timestamp field to each row. When you do this, Access uses an optimistic locking strategy and is able to recognise if you’re altering the same version of the record. Timestamps heve been supported since (at least) Access 95.

Advertisements