Enumerating Tables

The Tables collection in the ADOX object model offers an easy route for enumerating tables in an Access database. Table 3-1 shows that the Tables collection contains several types of tables. You might prefer to filter out one or more of these table types during a typical enumeration task. Besides the ADOX library, you can also use AccessObject objects to enumerate tables. AccessObject object types correspond to major elements within an Access database file. The next section in this chapter concentrates on AccessObject objects for tables.

Listing All Table Types in a Catalog

The first code sample for enumerating tables lists all the tables within a catalog's ActiveConnection property setting. This setting is just a connection string that points at a data source through an ADO data provider. The sample consists of two procedures. The first procedure designates a data source argument for the connection string. The listing that appears here sets the string variable str1 to the path and filename for the Northwind database. You can designate any Access database file that you prefer to use instead of the Northwind database. For example, this listing contains an alternate file named MyNewDB that is commented out. This file was generated in the preceding sample.

 SubCallListTablesRaw() Dimstr1AsString     'RunwitheitherbackupcopyofNorthwindorMyNewDB.mdb str1="C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb" 'str1="C:\Access11Files\Chapter03\MyNewDB.mdb" ListTablesRawstr1     EndSub     SubListTablesRaw(str1) Dimcat1AsADOX.Catalog Dimtbl1AsADOX.Table Dimstr2AsString Dimstr3AsString DimmwdAsInteger     'Instantiateacatalog,andpointittothetargetdatabase. Setcat1=NewADOX.Catalog cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1     'Computethelengthofthelongesttablenameinthedatabase. 'Assumeminimumlengthof5characters. mwd=5 ForEachtbl1Incat1.Tables IfLen(tbl1.Name)>mwdThenmwd=Len(tbl1.Name) Nexttbl1 mwd=mwd+1     'PrintarowtotheImmediatewindowcontainingthename 'andtypeofmemberinthecatalog'sTablescollection ForEachtbl1Incat1.Tables str2=tbl1.Name str3=String(mwd-Len(str2),"") Debug.Printstr2&str3&tbl1.Type Nexttbl1     'Cleanupobjects Setcat1=Nothing     EndSub 

After its declarations, the second procedure instantiates a Catalog object and sets its ActiveConnection property to the path and filename passed from the first procedure. Next, the second procedure passes through the members of the Tables collection to compute the longest table name. This permits the next block of code to space content evenly across the Immediate window for any table name in the catalog. Figure 3-2 shows the output to the Immediate window from the Northwind database. Notice the first column lists many more tables than you normally see in the Database window when you open the Northwind database. The second column shows each table type. This enumeration lists all the table types in the Northwind database but not all possible table types. Review Table 3-1 for a complete list of the possible table types along with a brief description of each. Those tables with a TABLE type specification appear in the Database window by default. Recall from Table 3-1 that views are virtual tables. The term virtual table correctly conveys the notion that a view is not actually a table. Instead, it is a SQL statement that returns a rowset, which it can present in a datasheet ”just like the rowset contained in a real table.

Figure 3.2: Output from the CallListTablesRaw and ListTablesRaw procedures for a clean backup copy of the Northwind database.

The output of MyNewDB might surprise you. Running CallListTablesRaw on MyNewDB generates output even before we populate the database file with any user -defined tables, such as those with a type of TABLE , LINK , or PASS-THROUGH . The listing of tables for the empty MyNewDB database consists of the SYSTEM TABLE type. This table category depends on system-generated activity, as opposed to end-user activity or developer activity.

Filtering Table Types Before Enumerating

The preceding sample includes some of the resources that table enumeration can deliver to an application. However, instead of having a broad array of every table type, you can specify the return of a small subset of the total tables by designating the return of just one table type. By implementing this capability, you can gather precise information about the tables in any Access database file on a local area network (LAN) or wide area network (WAN). The reduced number of items associated with a filtered subset makes for a better record source for a combo box or list box. User interfaces work best when they show users only the information that they need to view.

The next pair of procedures implements filtering to return just one type of table. The first procedure has three variables for a user to set. The first is a string variable that designates the path and filename for an Access database file. You must also assign a value to a second string variable that specifies a table type to return from the search target. This next listing sets this second string variable to the TABLE type. Recall that this type of table includes just tables in the local database created by or for users. The third variable has a Boolean data type. Setting the Boolean variable to True in the first procedure causes the second procedure to return a list of tables in the current Access database file. A False Boolean value points the catalog at the database specified by the first string, str1 .

 SubCallListTablesTypeFilter() Dimstr1AsString Dimstr2AsString Dimbol1AsBoolean     str1="C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind.mdb" str2="TABLE" bol1=False ListTablesTypeFilterstr1,str2,bol1     EndSub     SubListTablesTypeFilter(str1AsString,_ str2AsString,bol1AsBoolean) Dimcat1AsADOX.Catalog Dimtbl1AsADOX.Table Dimstr3AsString Dimstr4AsString DimmwdAsInteger     'Instantiatecatalog,andconnecttoCurrentProject 'oranotherdatabase. Setcat1=NewADOX.Catalog Ifbol1=TrueThen Setcat1.ActiveConnection=_ CurrentProject.Connection Else cat1.ActiveConnection=_ "Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1 EndIf     'CreatereferencetoTablescollectionforcatalog, 'anddeterminelongesttablenameinthecatalog. mwd=5 ForEachtbl1Incat1.Tables If(tbl1.Type=str2)Then_ IfLen(tbl1.Name)>mwdThenmwd=Len(tbl1.Name) Nexttbl1 mwd=mwd+1     'Printtablenamesandtheirtype. ForEachtbl1Incat1.Tables Iftbl1.Type=str2Then str3=tbl1.Name str4=String(mwd-Len(str3),"") Debug.Printstr3&str4&tbl1.Type EndIf Nexttbl1     'Cleanupobjects. Setcat1=Nothing     EndSub 

The second procedure in this sample employs the design of the previous sample, with one major exception. This exception relates to how the sample develops a setting for the Catalog object's ActiveConnection property. An If Else statement assigns one of two data sources for the catalog's ActiveConnection property. The second procedure chooses a path from the If Else statement based on the Boolean value passed to it from the first procedure. By using a Select Case statement, the second procedure could easily enable the selection of any of a wide set of specified sources for the catalog's ActiveConnection property.

Printing Data for the Table Details View

The Details view in the Access Database window returns more than just the name of a table and its type. It also returns two other particularly useful bits of information, the table's date of creation and its last modification date. Because the Table object exposes DateCreated and DateModified properties, generating a display like the one in the Database window's Details view is relatively straightforward ”just make the width of the Description column zero after clicking the Details control on the Database window.

To keep the design of the next code listing simple, the first procedure passes just one argument to the second procedure. This argument is a string parameter that designates the filename and path for the target database file. The argument designates the Northwind_backup.mdb file. In my office, I use the Northwind_backup.mdb file when I want to work with an unedited version of the Northwind database. Recall that I initially referred to the Northwind_backup.mdb file in the "Connecting to Jet Databases" section of Chapter 1. The second procedure has hard-coded filters that return only user-defined tables in the target database. Because this sample generates four columns of output, it prints a row of headers that clarify what each column contains for the members of the tables collection.

 SubCallTablesDetail() Dimstr1AsString     str1="C:\ProgramFiles\MicrosoftOffice\"&_ "Office11\Samples\Northwind_backup.mdb" TablesDetailstr1     EndSub     SubTablesDetail(str1AsString) Dimcat1AsADOX.Catalog Dimtbl1AsTable DimmwdAsByte     'Instantiateacatalog,andpointitatthetargetdatabase. Setcat1=NewADOX.Catalog cat1.ActiveConnection="Provider=Microsoft.Jet.OLEDB.4.0;"&_ "DataSource="&str1     'Computethelengthofthelongesttablenameinthedatabase. 'Minimumlengthof10providesforthelengthofthecolumnheader. mwd=10 ForEachtbl1Incat1.Tables Iftbl1.Type="TABLE"Then_ IfLen(tbl1.Name)>mwdThenmwd=Len(tbl1.Name) Nexttbl1 mwd=mwd+1     'PrintarowofcolumnheaderstotheImmediatewindowbefore 'printingthename,datemodified,datecreated,andtypeofmembers 'inthecatalog'sTablescollection. Debug.Print"TableName"&String(mwd-Len("TableName"),"")&_ "DateModified"&String(22-Len("DateModified"),"")&_ "DateCreated"&String(22-Len("DateCreated"),"")&"Type" ForEachtbl1Incat1.Tables Iftbl1.Type="TA