Help With an Erroreous 'Database cannot lock...table in use' Error (1 Viewer)

[rjp99]

Apologies in advance for length of this post but I need to provide some background.

1. I am the SOLE database user
2. I designed a form structure consisting of:
   1. a subform (called 'rjMAIN_SF') embedded in
   2. a main form (called 'rjMAIN')
3. The subform (rjMAIN_SF) allows me to enter data into a table called 'TempDE'
4. In addition to displaying the subform, the main form (rjMAIN) displays info (from another table) I need to know to be able to enter correct data into the subform.
5. When I'm finished making the entry(s) into the subform (it's a 'multi-item' form), I click a command button on the MAIN form and it launches the following code:
   1. DoCmd.Close acForm, "rjMAIN_SF", acSaveYes
   2. DoCmd.Close acForm, "rjMAIN", acSaveYes

Everything works as intended to this point. I have the code take care of some other tasks, I try to delete the 'TempDE' table (i.e. the table supplying ‘rjMAIN_SF’) and get the ‘3211 Error – “Database could not lock ‘TempDE’ because it is in use by another person or process”.

I simply DO NOT understand why this is happening!

1. The only person using the database is me (and I certainly don’t have the ‘TempDE’ table open when this error occurs) and
2. the only process that EVER uses this table is the subform but by the time I issue the delete command, I’ve long since issued a “save and close” command (bullet 5.1 above) and this command should cause the table to be released.

Can anybody offer some insight/a solution to this problem?

 

[GinaWhipp]

Sounds like you may be leaving a Recordset open somewhere in your code. Depending on what you are doing you can't just close the Object you also need to close the Recordset because you opened it via code to run/use it. That is where I would start looking.

 

[spikepl]

Just for fun, look up the DoCmd.Close and check what acSaveYEs does. Not what you apparently think.

Your story is weird: you cannot close a subform embedded in a form. As to the rest of the story, do what Gina said or strip your db to bare essentials, attach it here, and say how to reproduce the error.

Oh - and there is a way, by incorrect referencing, to unwittigly open invisible instances of a form. But hard to say what is going on without seeing the real animal in action.

 

[rjp99]

Thanks, Spikepl. My database and the objects essential to this problem are attached.

If you look at the code in the module for the main (vs. the sub) forms, you'll see that I have commands that close the sub-forms while the given main forms are open. You can open and close the main forms and not get an error message. I assumed that because I didn't get an error message, the operation was performing as intended (I realize it's an assumption).

Your comment re: 'Docmd.Close and acSaveYes' piqued my interest. I expect that combination to close the form and save whatever was entered to the underlying table.

The object library provides the following example:

Example
The following example uses the Close method to close the form Order Review, saving any changes to the form without prompting:
Visual Basic for ApplicationsDoCmd.Close acForm, "Order Review", acSaveYes

I see that the combo saves the form and closes it. I wasn't explicit enough in my original thinking - the updates made to the underlying table are saved when one moves to the next record; not when the form is saved and closed. I take your point. If you think this is the cause of this error, I need you to explain it to me because I just not seeing it.

I am making another more implicit assumption when I use the command - I'm assuming that closing a form automatically 'releases' it. Is this assumption wrong?

 

[GinaWhipp]

Well, technically it is in use. You are running a query, see Block 02 and Block 04, and that query is still open when you try to delete the Table, albeit, in the Background but they are still open.

 

[rjp99]

Sounds like you may be leaving a Recordset open somewhere in your code. Depending on what you are doing you can't just close the Object you also need to close the Recordset because you opened it via code to run/use it. That is where I would start looking.

Thanks, GinaWhipp. I wrote up a reply earlier but I neglected to do something? so it didn't post. I do open a couple of recordsets in this routine but they are closed when the delete command is issued. Also, the table that won't delete is not one of the tables that used in the recordset.

I've posted the database and the objects relevant to this issue in my 2nd post on this thread. Please feel free to have a look at it. Thanks again!

 

[rjp99]

Well, technically it is in use. You are running a query, see Block 02 and Block 04, and that query is still open when you try to delete the Table, albeit, in the Background but they are still open.

Thanks again, Gina. I see you got the database and saw the problem.

I think this is an Access problem. To demonstrate why I think this is the case, note the sequence of events:

1. The code gets to the problem line
2. Choose 'Debug' from the error message box
3. Access takes you to the problem line
4. Press the F8 key
5. The code continues

If the problem object were really in use then why can you proceed by pressing the F8 key? Pressing the F8 key BEFORE addressing the problem should regenerate the error message, no?

 

[GinaWhipp]

By choosing the F8 you are saying ignore that and move on which, in effect, force the table to close so you can delete. No biggie BUT it would be better if you closed it and then delete. That said, why are you opening? I can't see where it's even used.

 

[GinaWhipp]

On another note... add Option Explicit to the top of your Modules under Option Compare Database then Compile and declare those variables.

 

[rjp99]

By choosing the F8 you are saying ignore that and move on which, in effect, force the table to close so you can delete. No biggie BUT it would be better if you closed it and then delete. That said, why are you opening? I can't see where it's even used.

Gina, I created a 'dummy' situation to provoke the 3211 error. I created a table to delete, attached a form to that table and created a code module with the Docmd.DeleteObject in it.

To provoke the 3211 error, I opened the form (attached to the dummy table) and then ran the code. When I got the error message, I chose 'Debug' and this took me to the problem code line. I hit 'F8' and the code did not execute; the error message reappeared. I hit 'Debug' and F8 several times; each time the result was the same.

Try it out (really easy to create the necessary objects and code). If you get a different result please let me know how you made it work. Thanks, again.

 

[GinaWhipp]

I could not get it to work... was only taking your word for it. F8 just ignored the error and everything stopped, so it never went past the offending line.

 

[rjp99]

The more I wrestle with this, the less sense it makes. When the code is in "step through" mode from the beginning, there will be NO error message.

To test this:

1. go to the code module and put a 'pause' on the BLOCK 05 code line
2. go to the launch form and click command button
3. when you data entry form opens, click the button (bottom) to close it
4. step through the rest of the routine

After doing this, remove the 'pause' and then perform steps 2 and 3.

Semi-automated code execution DOES NOT cause an error but completely automated execution DOES? How can this be?!

I thought that maybe the issue was that code was getting to the delete statement before the form had a chance to close so I added some code to make code execution halt for 15 seconds (this code is NOT in the dB I attached). Adding this code DID NOT fix the problem.

 

[GinaWhipp]

Hmm, that actually does make sense, perhaps it's a timing issue. Try adding...

DoEvents

It may be that it needs to fully complete one action before moving to the next.

 

[rjp99]

Where should I put the 'DoEvents' command? What does it do?

 

[rjp99]

Hmm, that actually does make sense, perhaps it's a timing issue. Try adding...

DoEvents

It may be that it needs to fully complete one action before moving to the next.

Great call, GinaWhipp! Works like a charm! I don't really understand what the command is doing but as long as it works, I'm all good!

 

[GinaWhipp]

Hmm, I took too long to answer!

DoEvents does exactly what it says, allows the code to finish one part *completely* before going to the next. Now, if you find your CPU is processing hard, a sometimes side effect of DoEvents, you can swap it out for a Wait() function. (Google it, it's all over the web).

 

[rjp99]

Hmm, I took too long to answer!

DoEvents does exactly what it says, allows the code to finish one part *completely* before going to the next. Now, if you find your CPU is processing hard, a sometimes side effect of DoEvents, you can swap it out for a Wait() function. (Google it, it's all over the web).

Thanks for all your help, GinaWhipp! Would've taken me a month of Sundays to figure it out without your help.

 

[GinaWhipp]

Glad to help... Pat yourself on the back too! It was your posts that help find the problem!