Runtime error 3211 using Access VBA

angie_angie
angie_angie used Ask the Experts™
on
Hi experts,

I encountered the run-time error 3211:
The databse engine couldn't lock 'TempTable' because it is in use by another person or process.

Currently, I built in the function in MS Access using VBA such that each time I click on "Update" button, a table "TempTable" will be imported from Excel. Then information I need will be picked up from "TempTable" and copied to other tables that I have. After this, the "TempTable" will be deleted after the command:

CurrentDb.Execute "DROP TABLE TempTable"

This run-time error happens each time when I try to click on "Update" for the second time. If I close the program, restart it and click on "Update", it works fine.

Can anyone help me explain the error? Any solutions on this?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It's probably inside the code that's tapping the TempTable.  Any Open Recordsets that may still be attached to it?  Might help to see the code you're using when you Click Update.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
{having a vague memory here}
If you edit/delete a table in VBA code, you need to do a refresh of the database window before you can use that table again.

With CurrentDb
   .Execute "DROP TABLE TempTable"
   Application.RefreshDatabaseWindow   '<-- This has to happen here
   .Execute "SELECT stuff INTO TempTable FROM StuffBox"
End With
Actually to amplify Jim's statement if your'e code is not seeing the table you may also need to Refresh the Tabledefs collection.  If you're using CurrentDb as your database object then that shouldn't be an issue but if you're using DbEngine()() then it well could be.  The DBEngeine()() does have a performance advantage in that it doesn't worry about keeping these containers refreshed, personally I prefer to use CurrentDb() if I have the choice so issues like that aren't issues I have to consider.

Either way though, if you add the table via code and have the TableDefs Window open you won't see the new table as Jim mentioned.  You can, however, refresh the window as suggested or click on a different container (Forms for example) then back again and the table should then appear.  Another thing that will casue the window to self correct (when dropping the table) is clicking on the table name that is no longer there.  If you do that the table will suddenly vanish as Access realizes it's displaying something that is no longer there.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I have tried your methods by refreshing the database window after I delete the table. The message doesn't appear if I click multiple times on "Update", however, if I perform other functions that I built and turn back to "Update", the same error appears again... Sigh

I have checked all the recordsets that I created in all other functions that I built, I did close them after use.

I'm clueless right now...
I think it would help to see the code under cmdUpdate.  Since that is where the problem occurs (repeated uses of) it just seems the place we have to look.  There must be something more.

Author

Commented:
Rick_Rickards,

I've figured it out. There is a form that has its row source from TempTable.

Thank you very much for your hint!
Always happy to help.  Had to be something still hanging on to it. ;)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial