Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Object Existence

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Published:
Updated:

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.
12
5,602 Views
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT

Comments (3)

conagramanrock star

Commented:
nice article. i'm sure i will use it as a reference. thanks
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
@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?
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
@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.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.