Find Database & Delete Object (2007)

Topic

New Reply

I am running a report from a temp table created by a make table query.

I want to write an if…then…else statement to ensure that the temp table is deleted before I run the make table query. How would I do this?

If ??????? then
DoCmd.RunSQL “DROP TABLE temptable”
else

end if

Reply | Quote

Replies

The simplest way is to suppress error messages while trying to delete the table:

On Error Resume Next
DoCmd.RunSQL …
On Error GoTo 0 ‘ or an error handler

Reply | Quote

In addition to Hans’ response, I do it this way

Dim strTempTable1 As String
strTempTable1 = "YourTableName"

'check temp table existence
If DCount("*", "MsysObjects", "[Name]='" & strTempTable1 & "'") = 1 Then
    'table exists - so delete
    DoCmd.DeleteObject acTable, strTempTable1
Else
    'table does not exist, do nothing
End If

'call temp table create Sub
Call CreateTempTable(strTempTable1)

Reply | Quote