Deleting a temporary table

I have a form which contains pushbuttons to run reports. One of the reports is based on multiple queries which create and update a temporary table, and then the temporary table is used to create the report.

I want to delete the temporary table either when the report preview window is closed, or if this is not possible, when the "Return to Main Menu" push button is pressed.

What is the best way to check if this temporary table exists, before I try to delete it, as I currently get an error if the table does not already exist.

Recogonize that every time you run the update query, it will automatically recreates the table. However I do have code to help you with your problem, but it is at home in another project and when I get there I send it to you, if you still want it.
S-

Hi Guys,

Update query does not recreate a table, only a MAKE-TABLE query does it.

Insert, update and delete queries do not delete a table, they're just for modifying data in them.

If the query that creates the records under the report is a make-table query, then it deletes the table having the same name as the query's target.

If SetWarnings=False, then it will NOT prompt you for confirmation before deleting an existing table. It just deletes it w/o any warnings.

If SetWarnings=True, then it will prompt for confirmation.

Application.SetWarnings property can be set from VB code.

To eliminate the run-time error you need to have an error handler code in the sub where the error occurs:
'****************
Sub Whatever

On Error Goto MyErr

' your code here

MyExit:
Exit Sub

MyErr:
MsgBox Err.Number & ", " & Err.Description
Resume MyExit

End Sub
'****************
When you run the code you will get the error number for 'Object not found'. Note the number, and modify the MyErr label to:
'****************
MyErr:
If Err.Number=[the_number_you_noted] Then
Resume Next
Else
MsgBox Err.Number & ", " & Err.Description
Resume MyExit
End If
'****************

TBÁrpi is right, I mistakenly said update when I meant Make-Table

S-

Thanks for your replies. I already had the code recommended by TBÁrpi in my database.

I have since worked out a way around my problem. I created a Public variable which is of type Boolean, and I set this to true after I have run my make-table query. I check this variable is set to true in the code for the pushbutton that closes the form, and if it is, I execute a DeleteObject command to delete the temporary table.

Regards,

Michael.