Checking If Table Exists

[Craig]

Is it possible to use VBA code to see if a table already
exists before creating a table? If so, what would the
syntax for that be? Thanks...

Craig

 

[Graeme Richardson]

Hi Craig, this function is one way to do it.

Public Function tableExists(ByVal vstrTableName As String) As Boolean
' Requires reference to DAO
On Error Resume Next

Dim db As DAO.Database: Set db = DBEngine.Workspaces(0).Databases(0)
Dim tdf As DAO.TableDef

db.TableDefs.Refresh
Set tdf = db.TableDefs(vstrTableName)

tableExists = (Err.Number = 0) ' Error generated if table not found

End Function

 

[Tim Ferguson]

Is it possible to use VBA code to see if a table already
exists before creating a table? If so, what would the
syntax for that be?

' catch the error, just in case
On Error Resume Next

' Now try to drop the table
db.Execute "DROP TABLE OldTable", dbFailOnError


' actually it's probably safe to ignore the error
' but this is how you would respond to it
If Err.Number <> 0 Then
MsgBox "Don't worry it wasn't there!"

End If

' reset normal error handling
On Error Goto 0


Hope that helps


Tim F