Drop a Table

[sanders720]

CurrentDb.Execute "DROP tblReleasedHistory2"

I am trying to apply this as the only line to my report close. Whenever a report closes, I want to delete the table.

There is no open recordset, or anything. I simply want to delete a table that was created prior to opening the report. The report is dependent on the data from the table, and the table is recreated seqnentially the next time the report is opened.

Any help with this issue is greatly appreciated.

 

[HitechUser]

If the table is bound you can not delete it. Just delete it before you "recreate it" (i.e. before you open the report form0..

Or maybe I a missing something....

 

[sanders720]

Is the above syntax correct for this?

 

[sanders720]

I suppose to be fancy, I should check to see if the table exists before I delete it.

After moving the command to just before the table creation, CurrentDb.Execute "DROP tblReleasedHistory2" produces a syntax error.

In addition to the correct symtax, how can I check for the tables initial existance?

Thanks again for your help.

 

[HitechUser]

Cut and paste what you need from here..

Microsoft

Good Luck..

 

[sanders720]

Okay, the function is great. But, the command to drop an existing table that I know exists still produces a syntax error.

CurrentDb.Execute "DROP tblReleasedHistory2" produces a syntax error.

Is this single line command the right way to do this, or is there more to it.

Thanks again!

 

[VBslammer]

DoCmd.DeleteObject acTable, "tblWhatever"

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

Or if you want to use SQL (action query):

DoCmd.RunSQL "DROP TABLE tblWhatever"

VBSlammer

Unemployed in Houston, Texas

 

[sanders720]

Thanks for the help on this, it's all working great!

 

[EBee]

Follow up question: how do you drop the table if the table is older than 30 days?

thank you

 

[PHV]

Set myDB = CurrentDB
If myDB.TableDefs("tblWhatever").DateCreated + 30 < Date Then
myDB.Execute "DROP TABLE tblWhatever"
End If
Set myDB = Nothing


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

 

[Tranquilo]

Maybe I'm missing something here, but why create and delete tables for reports? Isn't that just a burdensome way of creating a recordset?