Could not lock table

[agibsonsw]

Hello. Access 2003.

A button on one form runs a macro which runs a few queries (including update and make-table queries) then opens a second form. Another option on this second form runs a second macro which again runs a sequence of queries.
During the second process an error message appears 'The database engine could not lock table tblBlah because it is already in use by another person or process'.

The database is only used by one person and the table mentioned in the error message is not open at the time.

Any ideas how I can get around this? I suspect that Access thinks the queries haven't finished. I can step through the macro and it works ok. Thanks, Andy.

[HansV]

Does this problem only occur when you open the second form from the first one, or also if you open the second form by itself?

[agibsonsw]

HI. The problem still occurs from just the 2nd form.
I notice that the 2nd form has it's RecordSource as the table that is being made. However, the macro closes this form before it attempts to make the table, and then re-opens the form.
In stepping through the macro I can see the form close, count to ten.. but the same lock message appears. Any thoughts? Andy.

[HansV]

Even though you close the form, it's still in memory because you're executing the macro that was called from the form. So you should avoid calling the macro from this form; you could use an unbound form for this purpose.

[agibsonsw]

That makes sense. Great, thanks! Andy.

[agibsonsw]

Oops spoke too soon. Tried this but the same error message. Any other thoughts? Andy.

I created an unbound form and used it's open event to run the macro, then close itself. The original button then opens this form and starts the macro.

[HansV]

Does the error also occur if you run the macro directly from the database window?

[agibsonsw]

No, there is no error if the macro is run directly. Andy.

If I close the 2nd form and run the macro there is no problem. It seems the problem still lies with the form being open, or in memory somehow? Andy.

[HansV]

Are you sure that the new form you tried was unbound, i.e. its Record Source property was blank?

[agibsonsw]

Yes, it's unbound. Andy.

[HansV]

I'm out of ideas for the moment. Could you attach a stripped down, compacted and zipped copy of the database to a reply? Thanks in advance.

[agibsonsw]

I'll obtain a suitable copy and get back to you. Thanks ,Andy.

[agibsonsw]

While waiting for a cut-down copy, I notice that if I have both forms open and run the macro from the database window it works fine. But if I drag the macro
to a toolbar and click it form there it fails. Is there a difference between running the macro from the database window or a toolbar? Thanks, Andy.

[HansV]

There shouldn't be a difference...

[agibsonsw]

Hello.
I've obtained a cut-down version of the database and compressed it. But it's just under an MB in size.
Is there someway I can upload this file? It exceeds your 256 Kb limit. Andy.

[HansV]

The zip file is more than 256 KB?
If so, you can send it to the admin e-mail address listed at the bottom of each thread and on the Portal.

[HansV]

Hi Andy,

I have received your e-mail.
The database is rather complex, so it's too difficult for me to understand what's going on exactly, but I suspect the problem is that by the time the make table query is run from the macro, you have reopened the from FrmVizACI. Although this form is unbound, it contains two combo boxes that have SQL statements as row source. Probably one of the tables involved directly or indirectly in these SQL statements is also involved in one of the make-table queries. I'd do a thorough search (I got lost in the seemingly endless series of macros, queries, tables etc.)