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

Question

I'm maintaining a multiuser Access 2000 DB linked to an MSSQL2000 database, not written by me.

The database design is very poor, so you'll have to bear with me.

On the 'Customer' form there's a 'Customer_ID' field that by default needs to get the next available customer ID, but the user has the option of overriding this choice with an existing customer ID.

Now, the Customer_ID field is not the PK of the Customer table. It's also not unique.

If a customer calls twice to submit a job, the table will get two records, each with the same customer information, and the same customer ID.

If a user creates a new ticket, Access does a quick lookup for the next available customer ID and fills it in. But it doesn't save the record. Obviously a problem - two users editing have to keep track of each others' work so they don't dupe up a customer ID.

So I want to modify the "new record" button so it saves the ticket right after creating a new one.

Problem is, when I test the change, I get "This record has been changed by another user since you started editing it".

Definitely no other users on the DB. The 'other user' was presumably my forced save.

Any ideas?

Answer 1

I would keep track of whether the user has overridden the new customer_id with a value of their own. If they haven't, then your app should be able to check for a duplicate right before saving and just self-increment again, and the user didn't mind taking the default. Maybe even some indicator to the user that you had to automatically choose a different value.

Answer 2

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

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

Answer 3

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

Answer 4

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.