Check if object exists

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Check if object exists

Post by agibsonsw »

Hello. Access 2010 (and, I believe, 2007).

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 :cheers:. 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
But I wouldn't be hurt if someone discovered a flaw in my plan.. Regards, Andy.

Added: BTW Could probably be easily extended to check for the existence of Macros or Modules, etc.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Check if object exists

Post by HansV »

If you're going to use On Error Resume Next and the AllTables etc. collections, you only need to try to refer to the item:

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 obj As AccessObject
    
    On Error Resume Next
    Select Case objType
        Case acTable
            Set obj = CurrentData.AllTables(strName)
        Case acQuery
            Set obj = CurrentData.AllQueries(strName)
        Case acForm
            Set obj = CurrentProject.AllForms(strName)
        Case acReport
            Set obj = CurrentProject.AllReports(strName)
        Case Else
            ObjectExists = False    'otherwise, assume it doesn't exist
            Exit Function
    End Select
    ObjectExists = (Err.Number = 0)
End Function
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Check if object exists

Post by agibsonsw »

@Hans, thank you. Both good :cheers:
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.