CODEWITHSUNDEEP
sql-server-2008ms-accessms-access-2010
ChettDM
ChettDM

Reputation: 360

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

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.

Upvotes: 22

Views: 51942

Answers (8)

Wade
Wade

Reputation: 1

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)

Upvotes: 0

Andy
Andy

Reputation: 31

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.

Upvotes: 3

Jordan Ryder
Jordan Ryder

Reputation: 2822

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.

Upvotes: 3

Xavier
Xavier

Reputation: 1

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

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 32

sirvan
sirvan

Reputation: 61

use Compact & Repair Database

Upvotes: 6

Gord00
Gord00

Reputation: 1

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.

Upvotes: 0

IABHunt
IABHunt

Reputation: 11

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.

Upvotes: 1

Related Questions