Check to see if a table exists. If so delete it. 2

[rstitzel]

I need a function that will check the existence of a table. For example tbl_employeemaster. If tbl_employeemaster already exists I want to delete the table from the current database.

Thanks in advance for any help.

 

[PaulBricker]

Dim tdf as TableDef
For Each tdf in CurrentDb.TableDefs
If tdf.Name = "tbl_employeemaster" Then
DoCmd.DeleteObject tdf.Name
End If
Next

That should do it.

Paul

 

[PaulBricker]

Sorry there are two arguments for this method.
DoCmd.DeleteObject acTable, tdf.Name

Paul

 

[OurTone]

Here is a function that will tell you if a table exists:

Public Function TableExists(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
    Set db = CurrentDb
    TableExists = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.count - 1
        If strTableName = db.TableDefs(i).Name Then
            'Table Exists
            TableExists = True
            Exit For
        End If
    Next i
    Set db = Nothing
End Function

And you can use it like this:

If TableExists("tblEmployeeMaster") Then db.Execute "DROP TABLE tblEmployeeMaster"