Access Cookbook - Ken Getz [136]
Case "Reports"
strObjType = "Report"
Case Else
strObjType = ""
End Select
' If this isn't one of the important containers, don't
' bother listing documents.
If strObjType <> "" Then
con.Documents.Refresh
For Each doc In con.Documents
' You can't backup the current form, since it's open.
If Not (doc.Name = Me.Name And con.Name = "Forms") Then
fReturn = SaveToCollection(strObjType, doc.Name, doc.DateCreated, _
doc.LastUpdated)
End If
Next doc
End If
Next con
FillObjCollection = mcolInfo.Count
End Function
The purpose of FillObjCollection is to fill a Collection object with a list of the names of each database container object, the type of each object, the date and time each object was created, and the date and time each object was last modified. Each item within this collection is an instance of the Info class, defined in the sample database. (Although the use of user-defined classes is beyond the scope of this book, you can investigate the Info class and see that it's quite simple. It behaves just like any other object available as part of Access or VBA—the only difference is that it's defined within your project.) In order to gather the necessary information, the code must work through all the available objects. This is accomplished by "walking" the Containers collection of the current database and working with the objects in each of the containers. There are eight different containers in the Containers collection, which are summarized in Table 6-7.
Table 6-7. The Containers collection
Container
Contains these documents
Backup documents?
Databases
General information about the database
No
Forms
Saved forms
Yes
Modules
Saved modules
Yes
Relationships
Enforced relationships
No
Reports
Saved reports
Yes
Scripts
Saved macros
Yes
SysRel
Unenforced relationships
No
Tables
Saved tables and queries
Yes
Because you are interested in backing up only the objects that appear in the Access database container, the function should ignore any containers in Table 6-7 for which "Backup documents" is No.
FillObjArray places the list box headings in the first item of the array:
' Set up the first row of field names
Call SaveToCollection("Type", "Name", "DateCreated", _
"LastUpdated")
We want the information in this first row to become the headings of the list box, so we set the ColumnHeads property of the list box to Yes. This setting tells Access to freeze the first row of the list box so that it doesn't scroll with the other rows. In addition, you cannot select this special row.
The function needs to walk the collections storing away the information that will appear in the list box. This should be relatively simple, but there is one complicating factor: the Tables container includes both tables and queries, mixed together in unsorted order. Fortunately, there's an alternate method for getting separate lists of tables and queries in the database. Instead of using the Tables container, FillObjCollection walks the TableDefs and QueryDefs collections to extract the necessary information:
' Special case TableDefs
db.TableDefs.Refresh
For Each tdf In db.TableDefs
' Only include non-system tables
If Not (tdf.Attributes And dbSystemObject) <> 0 Then
Call SaveToCollection("Table", tdf.Name, tdf.DateCreated, _
tdf.LastUpdated)
End If
Next tdf
' Special case QueryDefs
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
Call SaveToCollection("Query", qdf.Name, qdf.DateCreated, _
qdf.LastUpdated)
Next qdf
The TableDefs collection requires an additional test to exclude the normally hidden system tables from the list.
With the tables and queries taken care of, the function can now walk the remaining container collections for macros, forms, modules, and reports:
' Iterate through remaining containers of interest
' and then each document within the container
For Each con In db.Containers
Select Case con.Name
Case "Scripts"
strObjType = "Macro"
Case "Forms"
strObjType = "Form"
Case "Modules"
strObjType = "Module"
Case "Reports"
strObjType