Access database engine stopped the process because you and another user are attempting to change the same data

Question

We recently Migrated an access database onto a SQL server. Upon completion we began testing the database using the front end access database we had (Our previous setup involved two access files, one for front end and one for back end.) We almost immediately received the error.

the Microsoft access database engine stopped the process because you and another user are attempting to change the same data at the same time.

Now we do have multiple people working on these front ends but at the time of receiving this message, I'm the only person accessing the data. My general process for testing it has been to insert the data using the form. then attempt to delete the data. I know for a fact the data is making it to the table and I know for a fact I'm the only one viewing or attempting to edit this information we used an ODBC connection to attach SQL server to the front end. Any suggestions or help is greatly appreciated, I will be monitoring this thread heavily so Expect any questions you ask me to be answered relatively fast.

Thetes

Are you updating in code and via a form at the same time by any chance? – Faye

Also, have you compacted & repaired recently to ensure your copy is clean. – Faye

I'm not updating in code, I'm only changing the information in the new record of the form and allowing access to handle everything. I have not compacted and repaired. Let me check that out. – Thetes

See also support.microsoft.com/… re bit data type. Presumably you have a primary key, too? – Faye

I compacted and repaired, but to no avail. – Thetes

"See also support.microsoft.com/… re bit data type. Presumably you have a primary key, too?" This was the answer, Now every thing is right with the world, Sorry just figured out where your user name was, Remou if you would be so kind as to submit that as an answer I will happily mark it as such so you can get the point benefit from it. – Thetes

Answer 1

In this article, you will find that the error can be caused by the bit data type:

This problem occurs if fields with a bit data type in the SQL Server-based database have been left blank. Microsoft Access interprets blank fields as fields that contain Null values, and the Jet database engine does not release them. As a result, the records remain locked and are not available for deletion.

Note that you must always have a primary key or unique key to update data from SQL Server.

Faye

This answer solved my instance of the problem. I am using ODBC to communicate with MySQL. – Carmelo

The support article for this is at: support.microsoft.com/en-us/kb/318882 -- not sure if that is what you originally linked to or not. – Hbeam

For me, the problem was caused by a bit field with no default value. Adding a default value to the bit field solved it. – Liberality

This answer has a more detailed explanation and SQL commands to implement a fix #72701665 – Chinchilla

Answer 2

use Compact & Repair Database

Incisor

Answer 3

I was getting this error even though I had no bit fields and no nullable fields. I stopped getting the error when I changed the DATETIME fields to SMALLDATETIME. The only thing I could think of was that Access mis-interprets the dates and then tricks itself into thinking that something else has updated the date. I thought this because I had seen another comment on a different thread that Access can round differently than SQL Server.

Abdicate

Latest Office 365 update to Access made mine no longer work. Changing the SQL backend table column to SMALLDATETIME made it all work again. Thanks for the tip. – Preciosity

Answer 4

I added a timestamp field to the table in SQL server and re-linked it in access and that did the trick. It also solved the edit issues I was having with the table. Hope this helps other folks as well. The table already had a primary key field but apparently also needs the timestamp field.

Charioteer

Answer 5

I've just installed Access 2016 and had that error trying to import access 2013 tables from an accdb.

Apparently the 2016 back-end engine is now SQL Server. I deleted a field I intended to use but never did, consequently it was full of Nulls. After that no problems.

Authentic

There is no change in the default database engine used for Access. for the 23+ years that Access has been around, it would be ASTOUNDING that a full change from the JET/ACE data engine to SQL server could occur without MASSIVE issues. The null bits issue with SQL server been documented for many many years. This issue does not apply to a accDB file based back end. Its certainly possible you are using SQL server, but there not a change to the basic access product, and code and sql from say 15 years ago works the same in 2016 (so no change been made). – Urbanna

Thanks for your reply Albert. I respect your comments and have been helped many times over the years by what you have written. I have just finished rechecking with Microsoft chat about this, and they have confirmed with me twice it is SQL server. You may have better contacts for clarifying this – Authentic

Answer 6

I have also had this problem as described above, thought I would post a reply as the Microsoft link is no longer working.

I migrated the back-end of an Access database to MySQL on an AWS server, and any related table I tried to add or delete records from would generate the error that is the title of this thread.

The solution for me was to remove a field which type was "BIT" as luckily this was an old field used before I redeveloped the database so could be removed.

Obstetric

Answer 7

try not to use GetDate() as your default value when creating table column in sql. that fixed my problem.

Cerargyrite

Answer 8

Another solution is to check you SQL table for (bit) datatype that do NOT have a default set for that column.

Set a default, (0 or 1)

Spokeswoman