Runtime error 3211 using Access VBA

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!
angie_angieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rick_RickardsCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
{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
0
Rick_RickardsCommented:
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.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

angie_angieAuthor 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...
0
Rick_RickardsCommented:
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.
0
angie_angieAuthor 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!
0
Rick_RickardsCommented:
Always happy to help.  Had to be something still hanging on to it. ;)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.