Microsoft Jet database engine stopped the process Error (1 Viewer)

[arnodys]

Hi All

My department has customer database using microsoft access with the main table being a linked table to SQL-Server database down in IT department.

I've only been recently made aware that the staffs have been having problem when trying to change or delete old data. It keeps on bringing up the error message

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

which is hardly the case since each staff are assigned only one customer to work on.

While it is technically possible to have many people working on the same data, business wise, it is impossible to do that.

I still come up with the same error even when the IT department confirms that no one accessing the SQL-Server table and I was the only one logged in.

This leads me to believe that there is something in the code that somehow preventing the table being edited or deleted. The problem is the database is so huge and was designed by my predecessor so I do not have any knowledge of the complete working of the codes behind it.

If it's the problem with the code, can anyone tell me what sort of codes caused this? If not, can anyone tell me what happened?

Any help would be most appreciated.


Thank you

 

[pbaldy]

The message can be deceptive, because the "other user" can be you. I've seen this happen when 2 different forms are open and based on the same table, or when a bound form is open and you try to update the same data in code. Check for one of those, and I'd guess the error comes up at a specific point (like a button click), so look at the code that runs at that point.

 

[arnodys]

Hi Paul, thanks for the tip. I will check for the specific points you mentioned. I do try however to edit the record directly from the table without any forms or query open. It still come up with the same error.

This is where it got me, when I tried to enter details directly to the table, it works just fine to edit and delete. Somehow, there is a code that locks the data from being editted or deleted after it is entered.

Cheers
Arnody

 

[The_Doc_Man]

The issue might also be in the way the buffers are laid out.

See, Access doesn't technically store records. It writes records into buffers and stores buffers. If it so happens that you have two tables opened in a JOIN or in some other way that potentially requires access to "split" the joined record between two tables and update them, it could get confused. For regular Access, that should not be a barrier, but I'm not going to swear that ODBC-linked Access always does that right. Also, it matters what query options you use when opening the recordset. There are table-type, dynaset, snapshot, forward-only, and a couple of other ways to open recordsets. Don't ask me which one you used when you open the table directly. Table-type, probably - but I wouldn't bet on it.

 

[Dennisk]

Another reason may be the lack of a primary key on the table. When Access links the SQL tables it needs to know how to uniquly identify each record. With Access tables it uses it's own internal bookmarks. If a PK is not present then Access may not be able to determine which record to update, and sometimes the error message you see is not the real error at all.