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

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.

Answer 2

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