I was looking for a way to test if a table, or other object, exists within the current database and came across sites similar to this. Most code I found either loops through the TableDefs collection, or creates references to the DAO or ADO libraries and tries to reference the table.
I prefer my following method and you're welcome to use (or modify it) if useful to you . Basically, 'IsLoaded' for an object will return either True or False, or generate an error if the object doesn't exist. So checking for an error will confirm if the object exists. It doesn't require looping or DAO/ADO object references.
Code: Select all
Function ObjectExists(strName As String, Optional objType As AcObjectType = acTable) As Boolean
'Returns True or False if the table, form, query, or report exists in the database.
'Only checks for object types: acTable (default), acQuery, acForm, acReport.
'Sample Usage: ?ObjectExists("tblSome"), or ?ObjectExists("tblSome",acTable)
Dim blnLoaded As Boolean 'needed to receive the IsLoaded value
On Error Resume Next
Select Case objType
Case acTable
blnLoaded = CurrentData.AllTables(strName).IsLoaded
Case acQuery
blnLoaded = CurrentData.AllQueries(strName).IsLoaded
Case acForm
blnLoaded = CurrentProject.AllForms(strName).IsLoaded
Case acReport
blnLoaded = CurrentProject.AllReports(strName).IsLoaded
Case Else
ObjectExists = False 'otherwise, assume it doesn't exist
Exit Function
End Select
If Err.Number <> 0 Then 'or, specifically,
'If Err.Number = 2467 Then
'"The expression you entered refers to an object that is closed
'or doesn't exist"
ObjectExists = False
ElseIf Err.Number = 0 Then
ObjectExists = True
End If
'or just: ObjectExists = (Err.Number = 0)
On Error GoTo 0 're-instate normal error handling (and Clear Err)
End Function
Added: BTW Could probably be easily extended to check for the existence of Macros or Modules, etc.