“The database engine could not lock table ‘…’ because it is already in use by another person or process” error in JET

When performing DML and DDL queries against an Access database you may encounter something similar to the following:

The database engine could not lock table ‘Orders’ because it is already in use by another person or process.

The error message gives some information about one likely cause of this error – somebody else is trying to modify the same table you are. However it’s also possible to get this error message even if you have exclusive access to the database. This example illustrates:

DbConnection connection = Program.OpenAceOleDbConnection("C:\\Temp\\Northwind.mdb");
DbTransaction transaction = connection.BeginTransaction();
DbCommand command = connection.CreateCommand();
command.Transaction = transaction;

command.CommandText = "DELETE FROM [Orders] WHERE [Order ID] < 4000";
command.ExecuteNonQuery();

command.CommandText = "ALTER TABLE [Orders] ADD COLUMN [Test] INT";
command.ExecuteNonQuery();

transaction.Rollback();
connection.Close();

As soon as the second query begins to execute the error is raised:

JET DDL - Locking Exception

JET DDL – Locking Exception

The error message can be produced using JET, ACE and DAO. It is not raised consistently, it appears to be affected by whether the first query modifies row data pages partially or completely. Furthermore, it is only raised when executing queries inside a transaction.

I have not found a good workaround for this problem. If possible, reorder your queries so that the DDL is performed first. Another option is to perform the queries outside of a transaction. In my case, I took advantage of JET’s file-based format and the fact that my application would always have exclusive access to that file and emulated transactions by copying the database to a temporary location, performing the queries and then copying it back.

Update

Q331594 seems to come pretty close to describing this behaviour.

Advertisements

One thought on ““The database engine could not lock table ‘…’ because it is already in use by another person or process” error in JET

  1. Thanks for this – I had been wracking my brains on the same issue, and the same solution (file-copying “transactions”) worked for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s