Unable to delete table.

[danilof]

Hi
Grateful for any help please.

I am coding vba in an access 97 database.

I have created a temp table from which I have always extracted data at a stage in my application and then deleted it.

As a change to the functionality of my application I now use a query based on this table as a data source for a form where some of the temp table data is amended. I also use a recordset of the table to count the records.

I close the recordset and close the form, but am not now allowed to delete the table as it is "already in use by another person or process". This happens whether I actually amend any data in the table or not.

I can find nowhere where I think the table is in use and do not know how to find out from Access itself what is still using the table.

I have also closed the table (although I don't actually know what that does), but his makes no difference.

Any help would be greatly appreciated.

Danilof

 

[PHV]

Be sure that the Recordset is closed and then set to nothing.
Have you tried this ?
DoCmd.RunSQL "DROP TABLE [name of temp table]"
Another way is to empty the table instead of delete it:
DoCmd.RunSQL "DELETE FROM [name of temp table]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[danilof]

PHV - Many thanks for your help.

Have checked in the debug window that recordset is set to nothing.

Have tried DROP TABLE but this is met with the same error.

Have tried (manually) deleting the attached query while code stopped at error. This doesn't affect the error.

Don't want to just empty table as it's name is unique & I'll end up with loadsa temp tables in the database.

cheers for now