Tag Archives: JET

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

Posted on by
1

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.

Advertisement

“Operation must use an updateable query” error in JET

Posted on by
Reply

When performing update queries against a Microsoft JET database, you may come across the following error:

Operation must use an updateable query

The Microsoft Knowledge Base has an article for the most obvious cause of this error, that the database file is not writable. However, you may also receive this error when trying to run an UPDATE query which contains a subquery:

UPDATE [Orders]
   SET [Status ID] = ( SELECT TOP 1 [Status ID]
                         FROM [Orders Status]
                        ORDER BY [Status ID] );

I encountered this error when trying to write an update query that was valid JETSQL and TSQL. Unfortunately it’s not possible to write the query in a way that is valid SQL on both platforms1 – to work around this error in JET you need to change the way the query is structured:

UPDATE [Orders]
       , ( SELECT TOP 1 [Status ID]
             FROM [Orders Status]
            ORDER BY [Status ID] ) AS [tmpSubquery]
   SET [Orders].[Status ID] = [tmpSubquery].[Status ID];

The query now runs, but we’re not out of the woods yet. Let’s try writing a similar query that would achieve the same thing:

UPDATE [Orders]
       , ( SELECT MIN([Status ID]) AS [Min Status ID]
             FROM [Orders Status] ) AS [tmpSubquery]
   SET [Orders].[Status ID] = [tmpSubquery].[Min Status ID];

This query does not run – it fails with the same error. The problem is our use of an aggregate function (in this case, MIN).

To work around this we must take a departure from ANSI SQL and use the Domain Aggregate functions in Access.

UPDATE [Orders]
   SET [Status ID] = DMin("[Status ID]", "[Orders Status]")

This works, and can even be extended to emulate a correlated subquery via each domain aggregate function’s optional criteria argument.

1 JETSQL and TSQL have differing syntax for update queries. TSQL requires you to use the FROM clause when the update involves multiple tables, JETSQL does not (in line with ANSI SQL). Thus to write an update query that works on multiple platforms you need to use a subquery.

Update

Turns out there is a Knowledge Base article for this problem, for Access 2.0, and describes 3 workarounds. The post above describes the third workaround. Note that the first workaround may not work if you’re inside a transaction (it uses DDL), so I recommend looking at workarounds two and three.