Today I wanted to check whether a table existed in a MS-Access database from a macro define in a MS-Access module.

The test opens the specified database (databaseName) and from that object checks wether a table definition exists (name). I wonder whether there would be a better way to go through the list of items and just know whether an item with that name exists or not instead of generating an error (because the underlaying system will anyway search that list of items for the name, although it may be faster this way in visual basic.)

There is the function I wrote (sample usage: if TableExists("path/to/db.mdb", "tableName") Then ...)

Function TableExists(databaseName As String, name As String) As Boolean
  Dim TableDb As Database
  Dim TableDefinition As TableDef

  TableExists = False

  If Len(databaseName) = 0 Or Len(name) = 0 Then
    Exit Function
  End If

  On Error Goto CheckError
  Set TableDb = OpenDatabase(databaseName, , True)
  Set TableDefinition = TableDb.TableDefs(name)
  TableExists = True

ExitFunction:
  On Error Resume Next
  Exit Function

CheckError:
  If Err.Number <> 3265 Then
    MsgBox "Error #" & Err.Number & " occured: " & Err.Description
  End If
  Resume ExitFunction
End Function

The error number 3265 means that the name was not found in the collection. This means the table was not found. Note that if you pass an invalid database name or table name (empty strings) then no error is generated and False is returned.

If you have problems with the "As Database" or "As TableDef" then just remove those. It is not required.

The parameters to the function use "As String". That's also not required. At times a string doesn't quite look like a string... (don't ask!?)