Linked Access DB "record has been changed by another user"

This is an old question, which I've come across from Google, so I will submit my answer.

In the ODBC driver, make sure to turn on row versioning. If the table is already in Access, you'll have to drop it and re-link to the source table.

You should be able to tell if you have row versioning enabled because Access should add a column to your table called xmin.

Take a look at your linked table in SQL Server 2000. Does it have a field containing the bit datatype? Access will give you this error message in a linked table scenario if you have a bit field which does not have a default value.

It might not be what's wrong in your case, but I've experienced the same in an Access 2007 database and tracked the problem to a bit field with no default value.

I have seen this behaviour before and this fixed it for me:

Try to add a TimeStamp field to the table (just add this field and update your linked tables. You do not need to fill this field with any kind of data).

The error you're getting usually happens when:

1. you are editing a record in a form and the form is dirty (i.e., edits not saved),

AND

2. you run code that uses DAO or ADO to run SQL to update the same record.

To Jet, that's two "users", because it's two different edit operations. The underlying table has been updated by the SQL update, while the data in the form buffer is now out of date.

The usual solution is to force a save before running the SQL update:

  If Me.Dirty Then
     Me.Dirty = False
  End If
  [run your SQL update here]

But if you're using forms to edit the record, you ought to do all updates in the form, rather than resorting to SQL to do the update.

The situation you describe with generating your own sequence ought to be done in this fashion:

1. user hits NEW RECORD button.

2. calc next sequence value and store it in a variable.

3. insert a new record with that sequence value via a SQL INSERT.

4a. if your form is bound to all the records in the table, requery the data editing form (assuming the NEW RECORD button is on the form where users edit the data), and use bookmark navigation to move to the new record with the sequence value that you stored in the variable in step 2.

4b. If your form is not bound to all the records (as it shouldn't be if it's a well-designed database), you would just change the recordsource of the form to load only the new record.

Another alternative is to avoid the SQL INSERT and requery (or resetting the recordsource) and simply add a new record in the existing form, set the sequence field to the new value and immediately save the record.

The key point is that for this to work in a multi-user environment, the record has to be saved just as soon as the sequence value is assigned to it -- you can't leave the record hanging out there unsaved, because that means the identical sequence value is available to other users, which is just asking for a disaster.