Two ways of checking whether a table exists

There are two ways to check whether a table exists in your database. The first, and easiest, is to make use of the MSysObjects system table. We'll make use of two fields: Name (the name of the object, in this case our table) and Type (tables have a type of 1). This function will return True if the specified table exists, False otherwise.

Function TableExists(strTable As String) As Boolean
    TableExists = (DCount("*","MSysObjects","Name='" & strTable & "' AND Type=1") > 0)
End Function

This is fine, but the DCount() function can be a bit slow. A slightly more long-winded solution is to enumerate through all tables in the database, stopping if one is found that matches the specified table. This is also slow the first time you run it, but is faster thereafter. Again, this function will return True if the specified table exists, False otherwise.

Function TableExists(strTable As String) As Boolean
    Dim db As Database
    Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    TableExists = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
        If strTable = db.TableDefs(i).Name Then 'Table exists
            TableExists = True
            Exit For
        End If
    Next i
    Set db = Nothing
End Function