Delete temp table in VBA

[RayH]

I have a set of queries to gather data from some tables and create a new temporary table that is used as a recordsource for a form.
Once the form is closed the data gathered is appended to another table.
Ok, this may not be the best method but it works.

My problem is that once the data has been appended I want to remove the temporary table but always get a message saying that the object is locked or process by another user.

I have tried the following:
set the recordsource= "" for the form
then
DoCmd.Runsql "drop tmp_table;"
or
DoCmd.DeleteObject acTable, tmp_table

I still get the message if I run these commands on another form once the orignal form has been closed.
These are the recommended methods from Microsoft themselves according to the knowledge base.
Where am I going wrong?
Why does the table remain locked once the form has been close?
Thank you

 

[Moniker]

Set tmp_table = Nothing
DoCmd.DeleteObject acTable, "tmp_table"

As an alternative, you can just keep the temporary table's structure and clear its contents:

CurrentDb.Execute "DELETE * FROM tmp_table;"

 

[RayH]

I have to remove the table as the name changes with each run (per user) so I cannot just remove the contents.

I just discovered this:
DoCmd.Close acTable, tmp_table

If I run this prior to the DeleteObject cmd it works fine.
Don't know why I didn't think of it earlier.

 

[Moniker]

At least it worked out.