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