MS Access problem on updating record

MS Access problem on updating record

MS Access problem on updating record: currently locked by user 'admin'.

Based on the transaction you are currently processing when the error occurs, that you are processing data for your company records which is according you only occurred when processing above 7 records and when you are processing a few records at about 1 or 2 it works fine. This was exactly the error found and reported as a bug in Microsoft Support site.

You have never mentioned that if this database was shared into the network or the database is stand-alone. By the way, sharing your database in different applications located at different computers doesn’t have problems. Problem occurs only when heavy traffic on the database was done. Let’s say 100 computers and applications where sharing on the database, that sounds so bad.

This problem was reported as a bug in Microsoft Support Site and it was published under the article Q331594. The error message occurs when you are editing and updating records at the same open transaction where you use the database connection Microsoft OLE Provider for Jet 4.0.

Solution: Instead of using connection Microsoft OLE Provider for Jet 4.0, use the Microsoft OLEDB Provider for ODBC Drivers.

Why it is happening?

Microsoft Access is a single user access although you can connect in multiple instances and working on tables like updates and delete in different record sets. While you are connected and opened a table in Microsoft Access, it creates locking records so that other instances will never distract the current events with the incoming event or actions.

Inserting, updating and deleting it takes as fast as milliseconds, that is why sometimes saving in multiple instances works fine. But the problem occurs when too many records are updating just in one instance where the traffic is possible too busy.

Like for example, when you are saving let’s say 100 records in just one batch. You put it in a loop in your visual basic statement. While looping, you did not close your connection and at the same time you are using the same record set. Although for every loop you made you did the update, the connection is still active.

If you are saving large data or multiple columns, the server of the Microsoft Access Database will take time before finishing the first job. In your loop which the connection was still active, you can send another job on the database which will be added on the queue of the database. Once this queue was full, it will result the error-locking message because the back-end is too busy updating the records.

While your application or the front end never stops sending jobs. But if you are practicing closing the connection after each transaction, traffic will move slowly but surely. When you are connecting on the Microsoft Access Database while there is a transaction being process, your back-end refuses your connection and accept it after the job was done.

If you want more information about this issue, you may read the article posted in the Microsoft Support page and you will see what I mean.