Team LiB
Previous Section Next Section

Using the AllTables Collection

The AllTables collection can enumerate tables in a style similar to that of the Tables collection in the ADOX library. Unlike the Tables collection, the AllTables collection does not require the creation of a special reference to the ADOX library. This is because the AllTables collection is a part of the Microsoft Access 2003 Object Library. Therefore, when you start Access, the AllTables collection is available automatically. Access refers to the members of the AllTables collection as AccessObject objects.

The Access library contains multiple Allxxx collections, including AllTables, AllForms, AllQueries, and AllReports. The AccessObject can refer to a member of any of these collections. You can use the Type property to distinguish among the members of these different collections. An AccessObject object has the same Name property setting as the database object to which it refers. For example, the AccessObject object named Categories corresponds to the Categories table in the Northwind database. You can use an AccessObject object to enumerate forms and reports even when they are closed. In addition, you can use an AccessObject object to determine whether the database object corresponding to that AccessObject object is open.

The similarities between the Allxxx collection members and the ADOX collection members should not obscure the fundamental distinctions among them. For example, the Type property for an ADOX Tables collection member denotes a type of table, such as local or linked. The Type property for an AllTables collection member identifies a type of AccessObject object, such as an object pointing at a table (rather than an object pointing at a form, for example). Therefore, the AllTables collection has no built-in feature for filtering tables by their type. In addition, the AllTables collection automatically excludes views, while the Tables collection includes them. (Recall that a view is a special type of stored query.) Even more critical is the fact that Allxxx collections exclusively apply to the current project or a project linked to the current project via a reference. However, the collections in the ADOX library can refer to any Access database file.

Printing Table Details with the AllTables Collection

The next sample enumerates the tables in the current project via the AllTables collection. Notice that the AllTables collection belongs to the CurrentData object in the Access Application object. The atc1 variable points to this collection. The program requires an AccessObject object to enumerate the members of the collection. The sample references the AccessObject object with the atb1 variable. Since AccessObject objects have DateModified and DateCreated properties, you can create a report similar to the one made for the Details view of tables in an Access Database window. The following listing shows the code to print the name, date last modified, date created, and type of AccessObject object for the members of the AllTables collection in the CurrentData object:

Sub TablesDetailFromAllTables()
Dim atb1 As Access.AccessObject
Dim atc1 As Object
Dim mwd As Integer
   
'Create object reference to AllTables
'in CurrentProject
Set atc1 = Application.CurrentData.AllTables
   
'Determine name of longest table
mwd = 10
For Each atb1 In atc1
    If Len(atb1.Name) > mwd Then mwd = Len(atb1.Name) 
Next atb1
mwd = mwd + 1
   
'Print header for table detail followed by
'a row for each table
Debug.Print "Table Name" & _
    String(mwd - Len("Table Name"), " ") & _
    "Date Modified" & String(24 - Len("Date Modified"), " ") & _
    "Date Created" & String(28 - Len("Date Created"), " ") & "Type"
For Each atb1 In atc1
    Debug.Print atb1.Name & _
        String(mwd - Len(atb1.Name), " ") & _
        atb1.DateModified, atb1.DateCreated, _
        IIf(atb1.Type = acTable, "Table", "Error")
Next atb1
   
End Sub

The members of the AllTables collection include local tables as well as linked tables based on ISAM (indexed sequential access method) and ODBC data sources. In addition, the AllTables members include system tables. Because the AllTables collection has no Type property that distinguishes among these different types of tables, there is no easy way to enumerate the tables of just one type. The output from the preceding code sample appears in Figure 3-4. By comparing this output with the output in Figure 3-2, you can see that the tables in Figure 3-4 include the system, Access, and other kinds of tables.

Click To expand
Figure 3.4: Output from the TablesDetailFromAllTables procedure. Contrast with the output in Figure 3-2 to appreciate the differences between the AllTables collection and the Tables collection.

Detecting When Tables Are Open

One of the main benefits of AccessObject objects is their ability to determine whether a matching database object with the same name is open or closed. The following sample uses the atb1 variable that points to an AccessObject object to tell whether a table is open (or loaded). If the table is open, the procedure prints the table's name before closing it without saving any changes. The sample repeats this process for each member in the AllTables collection.

Sub PrintAndCloseOpenTables()
Dim atb1 As Access.AccessObject
Dim atc1 As Object
   
'Create object reference to AllTables
'in CurrentProject
Set atc1 = Application.CurrentData.AllTables
   
'Loop through the members of the AllTables collection
Debug.Print "The following tables are open. " & _
    "I will close them without saving changes for you."
For Each atb1 In atc1
    If atb1.IsLoaded = True Then
        Debug.Print atb1.Name
        DoCmd.Close acTable, atb1.Name, acSaveNo
    End If
Next atb1
   
End Sub

Team LiB
Previous Section Next Section