<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Object Existence

Published on
15,470 Points
4,370 Views
11 Endorsements
Last Modified:
Awarded

Introduction

When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened.

If you wanted to inspect/iterate the Forms or Reports collection to determine of a form existed, you would only see those forms which had been opened.
 

Easy and Reliable Solution

The objects are listed in the MsysObjects table.  We can query the table, looking for a name and object type match.  For simplicity, I used a DLookUp() function.
 
Option Explicit

Public Enum ObjectType
    ot_Table = 1
    ot_AttachedTable = 6
    ot_Form = -32768
    ot_Query = 5
    ot_Report = -32764
    ot_Module = -32761
    ot_Macro = -32766
    ot_Relationship = 8
End Enum


Public Function ObjectExists(ByVal parmName, parmType As ObjectType) As Boolean
    If IsNull(DLookup("Name", "Msysobjects", "Name='" & Replace(parmName, "'", "''") & "' And Type=" & parmType)) Then
        ObjectExists = False
    Else
        ObjectExists = True
    End If
End Function

Open in new window

Notes:
* Since an Access object name might contain an apostrophe, I have to use the Replace() function to double-up any apostrophe characters in the object name parameter.
* Since DLookUp() returns Null in a not-found condition, I have to use the IsNull() function.

Using the ObjectExists() function
As you will see in the example below, the function returns a boolean value (True/False) and can be used as an expression that expects a boolean value.  If you open the Immediate window after importing the above code into your database, you can play with the routine.  You will have to supply two parameters.  The first parameter is a string and should be the name of an object in your database.  The second parameter is an Enum, so you will see the choices for the type of object you are seeking.

Examples from my database:
In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] table object does not exist. I also know that a [BigTable] report object does not exist.
?ObjectExists("BigTable", ot_Table)
True
?ObjectExists("Big'Table", ot_Table)
False
?ObjectExists("BigTable", ot_Report)
False

Open in new window

 

Applications

While answering a question about programmatically creating forms for all the tables, I needed to rename the forms from their default name (provided by the Form wizard) to a name related to the source table.  In order to avoid name collisions, I created this ObjectExists() function.
http://www.experts-exchange.com/Q_27223471.html
 
Option Explicit


Public Sub AutoFormAllTables()
    Dim tdf As TableDef
    Dim frm As Form
    Dim strFormname As String
    Dim strNewname As String
    Dim lngLoop As Long
    On Error Resume Next
    For Each tdf In DBEngine(0)(0).TableDefs
        If (Len(tdf.Connect) <> 0) Or (tdf.Name Like "Msys*") Then
        Else
            'Debug.Print tdf.Name
            DoCmd.SelectObject acTable, tdf.Name, True
            DoCmd.RunCommand acCmdNewObjectAutoForm
            DoCmd.RunCommand acCmdDesignView
            Set frm = Application.Forms(0)
            strFormname = frm.Name
            frm.DefaultView = 2 'datasheet
            DoCmd.Save acForm, strFormname
            DoCmd.Close acForm, strFormname
            If ObjectExists("frm" & tdf.Name, ot_Form) Then
                lngLoop = 0
                Do
                    lngLoop = lngLoop + 1
                Loop While ObjectExists("frm" & tdf.Name & "_" & lngLoop, ot_Form)
                DoCmd.Rename "frm" & tdf.Name & "_" & lngLoop, acForm, strFormname
            Else
                DoCmd.Rename "frm" & tdf.Name, acForm, strFormname
            End If
            
            Select Case Err
                Case 0
                Case 2501
                    
                    strNewname = InputBox("New name for form or cancel for no save", "New name prompt", "frm" & tdf.Name & "_" & Timer)
                    Err.Clear
                    If Len(strNewname) = 0 Then
                    Else
                        DoCmd.Rename strNewname, acForm, strFormname
                        If Err <> 0 Then
                            Err.Clear       'only one mulligan
                        End If
                    End If
                Case 7791
                    MsgBox "Error (" & Err & ") " & Err.Description, vbCritical, "frm" & tdf.Name & "Creation problem"
                    Err.Clear
                Case Else
                    MsgBox "Error (" & Err & ") " & Err.Description, vbCritical, "frm" & tdf.Name & "Unknown problem"
                    Err.Clear
                
            End Select
        End If
    Next
End Sub

Open in new window

 

Alternative Solutions


1. Error Trapping

In addition to the MsysObjects table, there are the Containers collection, the CurrentProject collections
(.AllForms, .AllMacros, .AllModules, .AllReports), and the database engine object collections (tabledefs and querydefs).  This Microsoft article tries to access the object, by name, within one of these collections and traps the error if the name isn't in the collection.

https://support.microsoft.com/en-us/kb/90989
 

2. Collection Iteration

In this approach, you would iterate through the appropriate collection -- Container or CurrentProject, comparing the item names.  When you find a matching name, you can stop iterating, returning a found condition indicator.  If you complete the iteration without a name match, you can return a not-found condition indicator.  If you have a lot of objects in your collections, you might find a noticeable performance bump.

Container Iteration Example:

Option Explicit

Public Enum ContainerType
    ct_DataAccessPages = 0
    ct_Databases = 1
    ct_Forms = 2
    ct_Modules = 3
    ct_Relationships = 4
    ct_Reports = 5
    ct_Scripts = 6
    ct_SysRel = 7
    ct_Tables = 8
End Enum

Public Function NameExistsInContainer(ByVal parmName, parmContainer As ContainerType) As Boolean
    Dim varItem As Variant
    For Each varItem In DBEngine(0)(0).Containers(parmContainer).Documents
        If varItem.Name = parmName Then
            NameExistsInContainer = True
            Exit Function
        End If
    Next
    NameExistsInContainer = False
End Function

Open in new window

 

3. Dictionary Lookup

If you do have a lot of items in a collection and need to do a lot of name matching, you could instantiate and populate a Dictionary object with the names in a collection and then use the dictionary's .Exists() method.  This approach isn't as simple as the other two workarounds, since you would need to create a dictionary, collection or array of Dictionary objects.  In the following example, I'm using two dictionary objects.

Dictionary Lookup Example
 
Option Explicit

Public Enum ContainerType
    ct_DataAccessPages = 0
    ct_Databases = 1
    ct_Forms = 2
    ct_Modules = 3
    ct_Relationships = 4
    ct_Reports = 5
    ct_Scripts = 6
    ct_SysRel = 7
    ct_Tables = 8
End Enum

Private dicContainers As Object     'Scripting.Dictionary

Public Function NameExistsInContainer_Fast(ByVal parmName, parmContainer As ContainerType, Optional parmReset As Boolean = False) As Boolean
    If dicContainers Is Nothing Then
        PopulateDicContainers
    End If
    NameExistsInContainer_Fast = dicContainers(parmContainer).Exists(parmName)
    
    If parmReset Then
        dicContainers.RemoveAll
        Set dicContainers = Nothing
    End If
End Function

Private Sub PopulateDicContainers()
    Dim dicNames As Object      'New Scripting.Dictionary
    Dim vContainer As Variant
    Dim vDocument As Variant
    Dim lngLoop As Long
    
    Set dicContainers = CreateObject("Scripting.Dictionary")
    
    For lngLoop = 0 To DBEngine(0)(0).Containers.Count - 1
        Set dicNames = CreateObject("Scripting.Dictionary")
        For Each vDocument In DBEngine(0)(0).Containers(lngLoop).Documents
            dicNames.Add vDocument.Name, 1
        Next
        dicContainers.Add lngLoop, dicNames
    Next

End Sub

Open in new window


Note: The default dictionary object comparison is case sensitive.
11
Comment
Author:aikimark
3 Comments
LVL 10

Expert Comment

by:conagraman
nice article. i'm sure i will use it as a reference. thanks
0
LVL 26

Expert Comment

by:Nick67
@aikimark

I didn't grasp this sentence in your article the first two or three times I looked it over.
(That apostrophe is invisible unless you are looking for it, or know it is there.  The eye just misses it)
<<In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] table object does not exist.>>
Perhaps
<<In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] (with an apostrophe between Big and Table) table object does not exist.>>
Would be a useful edit.

Also, with the Enum's you show in the code, you don't explicitly state where those values come from, which make them a bit like magic.
Having looked in MsysObjects myself, I know that in the first code sample you made MsysObjects visible and then figured out what the numbers in [MsysObjects].[Type] mean.
In the container code, how did you derive the Enum values you show there?
0
LVL 48

Author Comment

by:aikimark
@Nick67

Thanks for the feedback.  Maybe I should have used an Irish name so that readers would expect to see an apostrophe.

I found a partial list of MsysObjects type values during a web search.  I deduced the others by creating objects, such as a macro, in my test database and then seeing what appeared in the MsysObjects table.

I iterated the Container collections to determine the Enum values for Containers.
0

Featured Post

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Join & Write a Comment

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month