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?

Shambles

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.

Holarctic

This totally worked for me, I updated the null bit columns to zero and the error has gone away. – Verla

Thanks a million. What a strange behavior. – Protagonist

remember to make sure that the bit field can't be set to null or you will have to go with birger's answer. – Timid

You can see the Microsoft KB about this "behavior" over here: support.microsoft.com/kb/280730/EN-US – Irenics

This is what StackOverflow is for. Pre-Internet I could have spent weeks trying to solve this, and I'd never have thought of this answer. Just to add a couple of extra things: if you have a linked table, don't forget to run code in Access to refresh the link; and make sure the bit field in SQL Server has a value for all existing rows, as well as a default value for new ones. Thanks so much! – Llywellyn

Istari, thank you for saving my bacon, even in Access 2010 and MS SQL2008R2 this is still a bizarre issue that would have had me banging my head for weeks as Andy surmised. Note:- default value must be 1|0 (true|false) even though BIT allows NULLs, MS Access doesn't! – Churlish

Wow. Glad to see this answer is still relevant and helping folks out. – Holarctic

also, if the table already exists and has null values in the bit column after you create a default constraint on the table, you will have to replace the null values. – Authoritarian

This is an old post and currently I'm migrating an access system to MySQL. I get the same error, I don't have bit fields, and I have also added the timestamps and yet it hasn't resolved it for me. Hoping someone could help me out here – Purlieu

Worked for me. Working on a legacy VBA App with a mysql db which someone attempted to move to a new version of Access and MySQL! Ensuring the smallint and bit datatypes had default values worked. Caused a few issues due to it but the main problem was fixed. – Argyres

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

Minnie

This is actually an important suggestion. I put a timestamp field in all my SQL Server tables as a matter of course. – Battology

What if the link table also link in another access file that serve as the shared data? I tried to add timestamp column. Do you mean the name will be timestamp? – Ese

So to be clear, the field has to be named exactly "timestamp"? – Precipitous

The name doesn't matter, the datatype must by TimeStamp. – Minnie

Amazing, I have had this issue for a while and just adding the timestamp field type onto the table fixed the issue. I did not even add a default value for the field. – Nemesis

I'm beginning to hate MS Access linked tables more and more. I added a field of type timestamp to one table and re-linked it... worked like a charm. Second table with 283 columns doesn't work with this solution because Access only imports 255 columns. Grrrrr. – Prefer

Answer 3

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.
4. Refresh the form as follows:
   • 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.
   • 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.

Battology

That's actually pretty much the solution I tried. I saved the record right after the sequence number was calculated. But any field changes after that cause the error during the update. I even did a Me.Refresh and it didn't work. Hell I even stored the ID, did a Me.Requery and then moved back to that ID and that didn't work either. Same error! – Shambles

Then there's something else going on. The key point is that you either want to edit your data in the form, or via SQL. You don't want to do both, unless you know exactly what you're doing. – Battology

Answer 4

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.

Lust

Which ODBC drivers for which databases have this feature? I just checked ODBC setup for my system, and even SQL Server Express 2008 R2 lacks any such setting. In any event, it's normal practice for Access front ends to require that the server database have timestamp fields in all the tables, which I would think would obviate the need for your suggestion. – Battology

Ahh I was looking at the tags and it didn't show sql server - his question did (that's my fault). Row Versioning is an option in ODBC for PostgreSQL. It looks like SQL_CONCURRENCY is the answer in SQL Server (it mentions adding a timestamp to the table). On the other hand, I think Postgres uses an internal id oid. – Lust

Answer 5

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.

Libra

Answer 6

I've been back and forth to this post and a few others to find a resolve for this behaviour. I have a MS Access database linked to MySQL database. The problem was caused by an existing BeforeUpdate Trigger in MySQL phpmyadmin. In phpmyadmin go to the Structure of the table and at the bottom of the screen you would see the Triggers.

Cornstalk

Answer 7

I also had same problem. I was trying to update in a table using Spring MVC and hibernate. In my case the version column in the table contains a value more than 1( i.e. 3) however the update information in my update query had version value 1.

Chromatism

Answer 8

Our problems was that the access front end was trying to save an int (yes/no) into a mssql bit (0/1) field. Changing the mssql database to int fields worked like a charm.

Eyeopener

Answer 9

I just came accross another situation that generates this error. In a mysql table I had two date columns which had initially default value '0000-00-00'. Later it was changed to default NULL but many rows kept the value '0000-00-00'. I had to manually reset the values to NULL to stop the error.

It took a lot of time to figure out what was trigering the error, HTH someone else.

Bus

Answer 10

This error can also be thrown if the SQL Server table contains a datetime2 column (in my case with the default value of sysdatetime()). Changing the datatype back to datetime default current_timestamp stops the error.

Tit

Answer 11

It took me days to find this, but I also encountered another situation which produces this error. I have many SQL tables with date/time fields which have a default constraint using getdate(). I have found that when a table which has date/time values with milliseconds is opened for editing, MS Access will throw this error (I am presuming that MS Access doesn't like the milliseconds and is updating the opened recordset to remove them). To avoid this problem (for data where the milliseconds is unnecessary), I stripped the milliseconds off the existing rows and changed my default constraint to the following:

DEFAULT (dateadd(millisecond, -datepart(millisecond,getdate()),getdate()))

Lorenza