Detect is table exists in codE?

S

SirPoonga

I want to do something like
db.TableDefs.Delete "mytable"

But I need to detect if the table is there first.
 
M

Mark

Add a command button to a form and put this in the OnClick event; it will
check to see if the table exists, and will let you know the results:

Private Sub cmdKillTable_Click()
Dim tdf As DAO.TableDef
Dim blnExists As Boolean
Dim strSQL As String

blnExists = False
For Each tdf In CurrentDb.TableDefs
If tdf.Name = "mytable" Then
blnExists = True
Exit For
End If
Next tdf
If blnExists = False Then
MsgBox "Table 'mytable' does not exists.", , "Table not found"
Exit Sub
End If
strSQL = "DROP Table mytable"
On Error GoTo Err_KillTable
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "Table 'mytable' has been deleted"
Exit_KillTable:
Exit Sub
Err_KillTable:
MsgBox "Error " & Err.Number & ": " & Err.Description, ,
"cmdKillTable_Click"
Resume Exit_KillTable
End Sub
 
K

Ken Snell [MVP]

Why? Just trap for the error that will occur if it's not there:

On Error Resume Next
db.TableDefs.Delete "mytable"
Err.Clear
 
Top