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.
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.