Checking to see if a table exists before calling query to drop it 1

[KimmieB]

I am calling a query from a form that will drop a temporary working table. It re-creates it later in the process. I'd like to first check to see if the table exists to avoid the "table doesn't exist msg". Is the best way to loop through the current databases's tabledefs and check the name or is there a more elegant way?

 

[PHV]

Brute force method:
DoCmd.SetWarnings False
On Error Resume Next
DoCmd.RunSQL "DROP TABLE [your table name]"
On Error GoTo yourErrorHandler
DoCmd.SetWarnings True

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

 

[TheAceMan1]

How are ya KimmieB . . . . .

This should work:

[blue]   Criteria = "[Name] = '" & "[purple][b]YourTableName[/b][/purple]" & "'"
   
   If Not IsNull(dlookup("[Name", "MSysObjects", Criteria)) Then
      [green]'Your Table Deletion Code Here[/green]
   End If[/blue]

See Ya! . . . . . .