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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
Select allOpen 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
1:
2:
3:
4:
5:
6:
Select allOpen 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
Select allOpen in new window
Alternative Solutions
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.
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B90989In 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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
Select allOpen in new window
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
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
Select allOpen in new window
Note: The default dictionary object comparison is case sensitive.
by: conagraman on 2011-07-27 at 20:10:03ID: 30104