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:
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.
Q331594 seems to come pretty close to describing this behaviour.