Could not lock table
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Could not lock table
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.
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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
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?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
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.
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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
That makes sense. Great, thanks! Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
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.
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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
Does the error also occur if you run the macro directly from the database window?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
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.
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.
Last edited by agibsonsw on 14 Apr 2010, 15:39, edited 1 time in total.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
Are you sure that the new form you tried was unbound, i.e. its Record Source property was blank?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
Yes, it's unbound. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
I'll obtain a suitable copy and get back to you. Thanks ,Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
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.
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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Could not lock table
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.
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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
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.
If so, you can send it to the admin e-mail address listed at the bottom of each thread and on the Portal.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78534
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Could not lock table
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.)
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.)
Best wishes,
Hans
Hans