MS Access Linked To SQL Backend – Use of Timestamp Fields on Database you do not control
There are many cases when it is useful to use MS Access as a front end to data that is stored in a SQL database. However, there are a few traps associated with this. If you get an error “This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made”, you probably need to add a Timestamp field to your base SQL tables.
Now it’s fine to add a timestamp field to the database tables if you have access to the database – but what if you are using Access to link to a third-party database where you can’t change the base tables.
After scratching my head on this for a while and trying a number of workarounds, I identified a very simple method that is working very well for me to date.
I observed that the parent tables all had LastModified columns in them, and I wondered if I could just cast that column to a timestamp data type and do everything in a view. So I made a view of each table that I wanted to access through my MS Access application (a good practice anyway) and added another column to each view with the defintion of CAST(LastModified AS TIMESTAMP) As Timestamp.
Since making this change, I have never again received the above error with writing data from MSAccess to linked SQL tables. This solution may not work in every scenario, but it saved me from throwing away a heap of work that I had done in Access because it was looking like this issue was going to make my application unusable.