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:

 SubTablesDetailFromAllTables() Dimatb1AsAccess.AccessObject Dimatc1AsObject DimmwdAsInteger     'CreateobjectreferencetoAllTables 'inCurrentProject Setatc1=Application.CurrentData.AllTables     'Determinenameoflongesttable mwd=10 ForEachatb1Inatc1 IfLen(atb1.Name)>mwdThenmwd=Len(atb1.Name) Nextatb1 mwd=mwd+1     'Printheaderfortabledetailfollowedby 'arowforeachtable Debug.Print"TableName"&_ String(mwd-Len("TableName"),"")&_ "DateModified"&String(24-Len("DateModified"),"")&_ "DateCreated"&String(28-Len("DateCreated"),"")&"Type" ForEachatb1Inatc1 Debug.Printatb1.Name&_ String(mwd-Len(atb1.Name),"")&_ atb1.DateModified,atb1.DateCreated,_ IIf(atb1.Type=acTable,"Table","Error") Nextatb1     EndSub

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.

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.

 SubPrintAndCloseOpenTables() Dimatb1AsAccess.AccessObject Dimatc1AsObject     'CreateobjectreferencetoAllTables 'inCurrentProject Setatc1=Application.CurrentData.AllTables     'LoopthroughthemembersoftheAllTablescollection Debug.Print"Thefollowingtablesareopen."&_ "Iwillclosethemwithoutsavingchangesforyou." ForEachatb1Inatc1 Ifatb1.IsLoaded=TrueThen Debug.Printatb1.Name DoCmd.CloseacTable,atb1.Name,acSaveNo EndIf Nextatb1     EndSub