Does a table exist in the database?

Does a table exist in the database?

If you want to check to see if a table exists, for example before deleting it, the fastest option is not to check whether it exists, but to delete it and either trap the error raised, or else to 'steam-roller' through the error raised on that line of code:

Sub sDeleteTable1()
On Error Resume Next
DoCmd.DeleteObject acTable, "tblName"
End Sub

The disadvantage with the above procedure is that it tries to carry on regardless of whether the table exists or not - for example, an error could be raised if the table is currently being used by another user. Therefore, this is better:

Sub sDeleteTable2()
    On Error GoTo E_Handle
    DoCmd.DeleteObject acTable,"tblName"
sExit:
    Exit Sub
E_Handle:
    Select Case Err.Number
        Case 3103
            Resume Next
        Case Else
            MsgBox Err.Description,vbOKOnly+vbCritical,"Error: " & Err.Number
            Resume sExit
    End Select
End Sub

You can also have code to check for this table. As ever, in Access, there are several ways of performing this check. Firstly, you could loop through the TableDefs collection:

Function fTableExists1(strTableName As String) As Boolean)
    Dim db As Database
    Dim tdf As TableDef
    Set db=DBEngine(0)(0)
    fTableExists1=False
    For Each tdf In db.TableDefs
        If tdf.Name=strTableName Then fTableExists1=True
    Next tdf
    Set db=Nothing
End Function

Another way of doing this is to query a system table, MSysObjects. The main problem with this sort of method is that although it works under A97 (and A2K I believe), as it is an undocumented feature, there is no guarantee that it will be supported under future versions. Anyway, here is the code:

Function fTableExists2(strTableName As String) As Boolean
fTableExists2 = Nz(DLookup("[Id]", "[MSysObjects]", "[Type]=1 AND [Name]='" & strTableName & "'"))
End Function

A third way is to use the IsObject function:

Function fTableExists3(strTableName As String) As Boolean
On Error Resume Next
fTableExists3 = IsObject(CurrentDb.TableDefs(strTableName))
End Function

Top

Last modified at 06/06/2006 15:02:09