Unanswered: "The record has been changed by another user"
I'm getting Microsoft Access's Write Conflict error message box whenever I try and change a record. The database is a front-end to an SQL Server DB and uses linked tables.
I added a record using the MS Access DB, if I then try to edit that record it works fine. However, I imported a load of data from the old MS Access DB using an INSERT..SELECT statement to transform the data. Although it copied it across, any of those records can no longer be edited.
What's causing this? I found a KB article that suggested updating a field to it's original contents that would fix a timestamping issue and allow further records to be edited. However, this didn't work.
In the end I decided I'd try and figure out how Access performs its locking + determines whether records have changed. I then decided I'd try and monitor timestamp values as they went between client and server to see if there was some kind of weird changing.
What I didn't realise was SQL Server/Access don't automatically use timestamp values, instead Access by default checks each field to see if its changed. By adding a timestamp field it works, just in case anyone else stumbles across this problem in future.