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.

Sub CallListTablesRaw()
Dim str1 As String
   
'Run with either backup copy of Northwind or MyNewDB.mdb
str1 = "C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb"
'str1 = "C:\Access11Files\Chapter03\MyNewDB.mdb"
ListTablesRaw str1
   
End Sub
   
Sub ListTablesRaw(str1)
Dim cat1 As ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim str2 As String
Dim str3 As String
Dim mwd As Integer
   
'Instantiate a catalog, and point it to the target database.
Set cat1 = New ADOX.Catalog
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & str1
   
'Compute the length of the longest table name in the database.
'Assume minimum length of 5 characters.
mwd = 5
For Each tbl1 In cat1.Tables
    If  Len(tbl1.Name) > mwd Then mwd = Len(tbl1.Name) 
Next tbl1
mwd = mwd + 1
   
'Print a row to the Immediate window containing the name 
'and type of member in the catalog's Tables collection
For Each tbl1 In cat1.Tables
    str2 = tbl1.Name
    str3 = String(mwd - Len(str2), " ")
    Debug.Print str2 & str3 & tbl1.Type
Next tbl1
   
'Clean up objects
Set cat1 = Nothing
   
End Sub

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.

Sub CallListTablesTypeFilter()
Dim str1 As String
Dim str2 As String
Dim bol1 As Boolean
   
str1 = "C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind.mdb"
str2 = "TABLE"
bol1 = False
ListTablesTypeFilter str1, str2, bol1
   
End Sub
   
Sub ListTablesTypeFilter(str1 As String, _
    str2 As String, bol1 As Boolean)
Dim cat1 As ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim str3 As String
Dim str4 As String
Dim mwd As Integer
   
'Instantiate catalog, and connect to CurrentProject
'or another database.
Set cat1 = New ADOX.Catalog
If bol1 = True Then
    Set cat1.ActiveConnection = _
        CurrentProject.Connection
Else
    cat1.ActiveConnection = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & str1
End If
   
'Create reference to Tables collection for catalog,
'and determine longest table name in the catalog.
mwd = 5
For Each tbl1 In cat1.Tables
    If (tbl1.Type = str2) Then _
        If Len(tbl1.Name) > mwd Then mwd = Len(tbl1.Name)
Next tbl1
mwd = mwd + 1
   
'Print table names and their type.
For Each tbl1 In cat1.Tables
    If tbl1.Type = str2 Then
        str3 = tbl1.Name
        str4 = String(mwd - Len(str3), " ")
        Debug.Print str3 & str4 & tbl1.Type
    End If
Next tbl1
   
'Clean up objects.
Set cat1 = Nothing
   
End Sub

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.

Sub CallTablesDetail()
Dim str1 As String
   
str1 = "C:\Program Files\Microsoft Office\" & _
    "Office11\Samples\Northwind_backup.mdb"
TablesDetail str1
   
End Sub
   
Sub TablesDetail(str1 As String)
Dim cat1 As ADOX.Catalog
Dim tbl1 As Table
Dim mwd As Byte
   
'Instantiate a catalog, and point it at the target database.
Set cat1 = New ADOX.Catalog
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & str1
   
'Compute the length of the longest table name in the database.
'Minimum length of 10 provides for the length of the column header.
mwd = 10
For Each tbl1 In cat1.Tables
    If tbl1.Type = "TABLE" Then _
        If Len(tbl1.Name) > mwd Then mwd = Len(tbl1.Name)
Next tbl1
mwd = mwd + 1
   
'Print a row of column headers to the Immediate window before
'printing the name, date modified, date created, and type of members
'in the catalog's Tables collection.
Debug.Print "Table Name" & String(mwd - Len("Table Name"), " ") & _
    "Date Modified" & String(22 - Len("Date Modified"), " ") & _
    "Date Created" & String(22 - Len("Date Created"), " ") & "Type"
For Each tbl1 In cat1.Tables
    If tbl1.Type = "TA