Deleting tables from Code

[striker]

I have a form which has been based on a table. I want the table to be created when I open a form and then I want to be able to delete the table programmatically at some point during an access session.

The why's and wherefores of this are that I cannot get the data I want from a query. I've tried writting queries to do it and i just can't get exactly the right data in the right format without using a temporary table.

When I try to delete the table using the onclose event I get an error saying that the table is locked by a user or process. I can understand this because the form is still open.

So I tried opening a new form from the onclose event which remains hidden. I the put the delete table code on the onopen event for the hidden form but it still won't let me delete the table I get the same error.

How can I do this?


Thanks for any assistance.

 

[Fornatian]

Me.RecordSource = ""
Me.Requery
DoCmd.DeleteObject acTable, stDocName

Set the recordsource to blank, requery then try deleting the table

 

[cogent1]

Eh?

Why can't you get the data from a query? And why are you in a tearing hurry to delete the table? Can't you wait until the next time you run the make-table query?

 

[striker]

Cogent1.

I have tried getting the query data right but I am working with somebody elses database and haven't got the time or resources to redesign the tables. This is only a quick fix to address a particular need.

To get the same data i needed to run 7 seperate queries which were all dependent on each other followed by a union query which only seemed to work some of the time.

Hence the temporary table which is created at run time and I then want to delete it in code when the dbase is closed.

Fornation I will try that tomorrow.

Cheers for the suggestion.

 

[Rich]

Act in haste, repent at leisure?