Using the All Collections | Programming Microsoft Access 2000 (Microsoft Programming Series)

If you are the type of developer who likes to track your objects in a database project (most of us find this essential), you'll be happy to know that there is an AllModules collection, which is a counterpart to the AllForms and AllReports collections you learned about in Chapters 5 and 6. The members of the All collections are not database objects, such as forms, reports, and modules, but AccessObject objects that contain a minimal amount of detail about most types of saved objects in a database.

AccessObject Properties

You can quickly enumerate the AccessObject objects in any All collection. Since AccessObject objects point at saved objects, you cannot add or delete members. You perform these tasks through the open collections they point to.

When you encounter an AccessObject object that your application needs more detail on, you can use the IsLoaded and Name properties to examine the properties of the object the AccessObject object points to. These open object collections have a fuller set of properties and methods that are not available with the All collections.

AccessObject objects have a Type property that describes the type of AccessObject rather than the type of database object. The Type property value of any AllModules member is 5. This distinguishes an AccessObject member in the AllModules collection from one in the AllForms collection with a Type property value of 2. In either case, you cannot determine whether you are dealing with a class module or a form class module by simply examining the AccessObject member of the All collection. You must examine the Type property of a Module object and the HasModule property of a Form object.

The All Collections

There are seven All collections besides the AllModules, AllForms, and AllReports collections. This set of ten collections (see Figure 7-5) divides naturally into two sets of five each. The AllForms, AllReports, AllMacros, AllModules, and AllDataAccessPages collections are members of the CurrentProject and CodeProject objects in the Access Application object. The AllTables, AllQueries, AllViews, AllStoredProcedures, and AllDatabaseDiagrams collections are members of the CurrentData and CodeData objects in the Access Application object. When you designate AccessObject objects in any of the ten All collections, you must set a reference that points at the appropriate antecedent object. Failing to do so will generate an error.

click to view at full size.

Figure 7-5. The ten All collections and their hierarchical relationship to Project and Data objects.

The AllQueries, AllViews, AllStoredProcedures, and AllDatabaseDiagrams collections have restricted availability by Access file type. Recall that Access projects can be in a traditional .mdb file or in the new Access 2000 .adp file. (Chapter 4 touched on the .adp file type, and Chapter 12 will discuss it further.) The AllQueries collection is available in .mdb files but not .adp files. In contrast, you can tap the AllViews, AllStoredProcedures, and AllDatabaseDiagrams collections from .adp files but not from .mdb files. Your applications can still reference views and stored procedures in .mdb files by using the ADOX object library.

NOTE
You might wonder why Access 2000 permits Views and Procedures collections in .mdb files but does not offer AllViews and AllStoredProcedures in .mdb files. These two All collections were not exposed in .mdb files because of the need to satisfy higher priority requirements. Look for AllViews and AllStoredProcedures collections with .mdb files in future releases of, or updates to, Access.

Enumerating the All Collection Members

The following three procedures show the high degree of similarity in programming different All collections. The first procedure performs a simple enumeration of all the modules in the current project. Notice that it initially declares obj1 as an AccessObject type because it accepts the identity of elements in the AllModules collection, which contains AccessObject objects. Also note that the enumeration loop passes through the AllModules collection but the code reaches this collection through the Application and CurrentProject objects.

Sub EnumerateAllModules() Dim obj1 As AccessObject     For Each obj1 In Application.CurrentProject.AllModules         Debug.Print obj1.Name & vbTab & obj1.Type & _             vbTab & obj1.IsLoaded     Next obj1 End Sub Sub EnumerateAllForms() Dim obj1 As AccessObject     For Each obj1 In Application.CurrentProject.AllForms         Debug.Print obj1.Name & vbTab & obj1.Type & _             vbTab & obj1.IsLoaded     Next obj1 End Sub Sub EnumerateAllTables() Dim obj1 As AccessObject     For Each obj1 In Application.CurrentData.AllTables         Debug.Print obj1.Name & vbTab & obj1.Type & _             vbTab & obj1.IsLoaded     Next obj1 End Sub

The EnumerateAllForms and EnumerateAllTables procedures have the same structure as the EnumerateAllModules procedure. You should note some significant differences in content, however. First, the specific AccessObject collection changes from AllModules to AllForms in one procedure and AllTables in the other procedure. Second, the path to the AllTables collection passes through the CurrentData object rather than the CurrentProject object. If we were to switch the AllTables collection to either an AllViews or an AllStoredProcedures collection, the code would work in an .adp file but not in an .mdb file.

Adapting to .mdb and .adp File Types

Using the ProjectType property of the CurrentProject object, you can detect whether you are working with an .adp or an .mdb file. This lets you write single procedures that adapt to their environment. The following sample prints the names of all the views and stored procedures in an .adp file, but it switches to printing all the queries in an .mdb file. As you can see, the only trick required is to test for the value of the ProjectType property. The AccessObject Type property adds values by distinctly differentiating objects for views with a type value of 7 from objects pointing at stored procedures, whose type value is 9.

Sub EnumerateAllViews2() Dim obj1 As AccessObject, dbs1 As Object     Set dbs1 = Application.CurrentData     If Application.CurrentProject.ProjectType = acADP Then         For Each obj1 In dbs1.AllViews             Debug.Print obj1.Name & vbTab & obj1.Type & _                 vbTab & obj1.IsLoaded         Next obj1         For Each obj1 In dbs1.AllStoredProcedures             Debug.Print obj1.Name & vbTab & obj1.Type & _                 vbTab & obj1.IsLoaded         Next obj1     Else         For Each obj1 In dbs1.AllQueries             Debug.Print obj1.Name & vbTab & obj1.Type & _                 vbTab & obj1.IsLoaded         Next obj1     End If End Sub

Using AllForms and AllModules

The sample below uses the All collections and the corresponding collections of open modules and forms to develop a list of all the modules (by type) and the class modules for forms in a project. Since the property for denoting standard class modules is different from the one for class modules for forms, the code requires different expressions to test for standard class modules vs. class modules for forms.

Recall that modules have a Type property but forms have a HasModule property. The code must iterate through the members of the AllModules and AllForms collections because some, or even all, modules and forms can be closed. You check the IsLoaded status of the AccessObject objects in AllModules and AllForms to determine whether you need to open a module or form before assessing its module type, or to determine whether a form has a class module. The procedure recloses forms and modules after it examines them.

Sub ListAllModulesByTypeAndClassForms() Dim obj1 As AccessObject, dbs1 As Object Dim mod1 As Module, frm1 As Form          Set dbs1 = Application.CurrentProject 'Search for open AccessObject objects in AllModules collection. 'Open and reclose those that are not open.          For Each obj1 In dbs1.AllModules         If obj1.IsLoaded = True Then             ListTypeOfModule obj1.Name         Else             DoCmd.OpenModule obj1.Name             ListTypeOfModule obj1.Name             DoCmd.Close acModule, obj1.Name         End If     Next obj1 'Search for open AccessObject objects in AllForms collection. 'Open and reclose those that are not open.     For Each obj1 In dbs1.AllForms         If obj1.IsLoaded Then             DoesFormHaveModule obj1.Name         Else             DoCmd.openform obj1.Name             DoesFormHaveModule obj1.Name             DoCmd.Close acForm, obj1.Name         End If     Next obj1 End Sub Sub ListTypeOfModule(modname) Dim strType As String 'Decode module Type value.     If Modules(modname).Type = 0 Then         strType = "Standard Module"     Else         strType = "Class Module"     End If      'Print module name and type.     Debug.Print Modules(modname).Name & vbTab & strType End Sub Sub DoesFormHaveModule(frmname) 'Only print form name if it has a module.     If Forms(frmname).HasModule = True Then         Debug.Print frmname & vbTab & "Form Class Module"     End If End Sub