Recipe 4.2 Build an Object Inventory

4.2.1 Problem

To document your application, you'd like to be able to create a list of all the objects in your databases, including their owners, date of creation, and date of last update. You're sure you can do it manually, but is there a better way to create a table containing all this information?

4.2.2 Solution

Access's Data Access Objects (DAO) can give you the information you need. By programmatically working your way through each of Access's container collections, you can add a row to an inventory table for each object in your application, storing information about that object. You should be able to use the techniques for this operation to write your own code for enumerating other collections in Access. There are a few tricks along the way, which this solution discusses, but in general this is a straightforward project.

To create an object inventory for your applications, take only two steps:

Figure 4-4. The inventory-creating form once it's done its work on a sample database

This example form includes the Access system tables, which you may never have encountered. These tables are part of every Access database and are not cause for alarm. You can view them in the Database Explorer by choosing the Tools Options menu and turning on the Show System Objects option.

4.2.3 Discussion

How this solution works is a lot more interesting than the final product. The object inventory itself can be useful, but the steps involved in creating the inventory may be more useful to you in the long run. All the code examples used in this section come from the form module attached to zsfrmInventory (in 04-02.MDB).

When the form loads, or when you click the Rebuild Object Inventory button on zsfrmInventory, you execute the following code. (The "zs" prefix, by the way, reminds you that zsfrmInventory is a "system" form, used only by your application. The z forces this form to sort to the bottom of the database container so you won't get it confused with your "real" forms.)

Private Sub RebuildInventory( )
    On Error GoTo HandleErr
    DoCmd.Hourglass True

    Me.lstInventory.RowSource = ""
    Call CreateInventory
    Me.lstInventory.RowSource = "SELECT ID, Container, Name, " & _
     "Format([DateCreated],'mm/dd/yy (h:nn am/pm)') AS [Creation Date], " & _
     "Format([lastUpdated],'mm/dd/yy (h:nn am/pm)') AS [Last Updated], " & _
     "Owner FROM zstblInventory ORDER BY Container, Name;"

ExitHere:
    DoCmd.Hourglass False
    Exit Sub

HandleErr:
    Resume ExitHere
End Sub

This code turns on the hourglass cursor and sets the main list box's RowSource property to Null. (It must do this because it's about to call the CreateInventory procedure, which attempts to delete the table holding the data. If the list box were still bound to that table, the code couldn't delete the table—it would be locked!) It then calls the CreateInventory subroutine. This procedure fills zstblInventory with the object inventory, and it can take a few seconds to run. When it's done, the code resets the list box's RowSource property, resets the cursor, and exits.

4.2.3.1 Documenting all the containers

The CreateInventory subroutine first creates the zstblInventory table. If CreateTable succeeds, CreateInventory then calls the AddInventory procedure for each of the useful Access containers (Tables, Relationships, Forms, Reports, Scripts, and Modules) that represent user objects. (Tables and queries are lumped together in one container. As you'll see, it will take a bit of extra effort to distinguish them.) Because each of the AddInventory procedure calls writes to the status bar, CreateInventory clears out the status bar once it's done, using the Access SysCmd function. The following code fragment shows the CreateInventory subroutine:

Private Sub CreateInventory( )
    If (CreateTable( )) Then
        ' These routines use the status line,
        ' so clear it once everyone's done.
        Call AddInventory("Tables")
        Call AddInventory("Forms")
        Call AddInventory("Reports")
        Call AddInventory("Scripts")
        Call AddInventory("Modules")
        Call AddInventory("Relationships")

        ' Clear out the status bar.
        Call SysCmd(acSysCmdClearStatus)
    Else
        MsgBox "Unable to create zstblInventory."
    End If
End Sub

4.2.3.2 Creating the inventory table

The CreateTable function prepares the zstblInventory table to hold the current database's inventory. The code in CreateTable first attempts to delete zstblInventory (using the Drop Table SQL statement). If the table exists, the code will succeed. If it doesn't exist, the code will trigger a runtime error, but the error-handling code will allow the procedure to continue anyway. CreateTable then recreates the table from scratch by using a data definition language (DDL) query to create the table. (See the Solution in Recipe 1.15 for more information on DDL queries.) CreateTable returns True if it succeeds or False if it fails. The following is the complete source code for the CreateTable function:

Private Function CreateTable( ) As Boolean
    ' Return True on success, False otherwise.
    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Dim strSQL As String
    
    On Error GoTo HandleErr
    Set db = CurrentDb( )

    db.Execute "DROP TABLE zstblInventory"
    
    ' Create zstblInventory.
    strSQL = "CREATE TABLE zstblInventory (Name Text (255), " & _
     "Container Text (50), DateCreated DateTime, " & _
     "LastUpdated DateTime, Owner Text (50), " & _
     "ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL
    
    ' If you got here, you succeeded!
    db.TableDefs.Refresh
    CreateTable = True
    
ExitHere:
    Exit Function
    
HandleErr:
    Select Case Err
        Case 3376, 3011 ' Table or Object not found
            Resume Next
        Case Else
            CreateTable = False
    End Select
    Resume ExitHere
End Function

4.2.3.3 Documenting each container

The AddInventory subroutine is the heart of the inventory-creating operation. In Access, each database maintains a group of container objects, each of which contains a number of documents. These documents are the saved objects of the container's type, such as tables, relationships, forms, reports, scripts (macros), or modules. AddInventory looks at each document in each container, adds a new row to zstblInventory for each document, and copies the information contained in the document into the new row of the table. (All the code examples in this section come from AddInventory in zsfrmInventory's module.)

The first step AddInventory performs is to set up the necessary DAO object variables:

Set db = CurrentDb
Set con = db.Containers(strContainer)
Set rst = db.OpenRecordset("zstblInventory")

The code then loops through each document in the given container, gathering information about the documents:

For Each doc In con.Documents
...
Next doc

For each document in the Tables container, the code must first determine whether the given document is a table or query. To do this, it calls the IsTable function, which attempts to retrieve a reference to the requested object from the database's TableDefs collection. If this doesn't trigger a runtime error, that table must exist. Because attempting to retrieve a query's name from the TableDefs collection will certainly fail, you can use IsTable to determine if an element of the Tables container (which contains both tables and queries) is a table. The isTable function appears as follows:

Private Function IsTable(ByVal strName As String) As Boolean
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef

   On Error Resume Next

   Set db = CurrentDb( )

   ' See the following note for information on why this
   ' is commented out.
   ' db.Tabledefs.Refresh

   Set tdf = db.TableDefs(strName)
   IsTable = (Err.Number = 0)
   Err.Clear
End Function

Normally, before retrieving information about any Access persistent object collection (TableDefs, QueryDefs, etc.), you must refresh the collection. Because Access doesn't keep these collections up to date unless necessary, it's possible that a table recently added by a user in the user interface might not yet have been added to the TableDefs collection. In this case, you'll be calling IsTable repeatedly. To speed the operation of zsfrmInventory, the IsTable function used here does not use the Refresh method each time it's called; it counts on the caller to have refreshed the collection. In almost any other use than this one, you'd want to uncomment the call to the Refresh method in the previous code example and allow the code to refresh the collection before checking for the existence of a particular table.

This code fragment fills a string variable, strType, with the type of the current document. The type is one of Tables, Relationships, Queries, Forms, Reports, Scripts, or Modules.

If strContainer = "Tables" Then
    If IsTable(doc.Name) Then
        strType = "Tables"
    Else
        strType = "Queries"
    End If
Else
    strType = strContainer
End If

The value of strType will be written to zstblInventory along with the document information.

Caching Object References Note that the previous code sample uses an object variable, doc, to refer to the current document. The For Each...Next statement sets up this reference for you. This construct loops through every item in a collection, assigning a reference to each object as it loops. We could have use a simple For...Next loop, but that solution would have been less efficient. Because later code will refer to this particular document a number of times, it's more efficient to set up this direct reference than to ask Access to parse the general reference, con.Documents(intI), each time it needs to refer to the document. In general, any time you need to refer to an object more than once, you can make your code run a little better by setting an object variable to refer to that object. This will save Access from having to look up the object repeatedly.

Once AddInventory has determined the correct value for strType, it can add the information to zstblInventory. AddInventory retrieves the various properties of the document referred to by doc and copies them to the current row in zstblInventory, referred to by rst. Once it's done, it uses the recordset's Update method to commit the new row. This process is illustrated in the following code fragment from the AddInventory procedure:

rst.AddNew
    rst("Container") = strType
    rst("Owner") = doc.Owner
    rst("Name") = doc.Name
    rst("DateCreated") = doc.DateCreated
    rst("LastUpdated") = doc.LastUpdated
rst.Update

4.2.3.4 Avoiding errors

The list box on zsfrmInventory has the following expression as its RowSource property:

SELECT ID, Container, Name, 
 Format([DateCreated],"mm/dd/yy (h:nn am/pm)") AS [Creation Date],
 Format([lastUpdated],"mm/dd/yy (h:nn am/pm)") AS [Last Updated],
 Owner FROM zstblInventory ORDER BY Container, Name;"

There are two issues to consider here. First, the SQL string used as the RowSource pulls data from zstblInventory. It's quite possible, though, that when you load the form, zstblInventory doesn't exist. To avoid this problem, we saved the form with the list box's RowSource set to a null value. When the form loads, it doesn't attempt to retrieve the data until the code has had time to create the table, as you can see in the RebuildInventory procedure shown earlier.

The second thing to bear in mind is that Access doesn't always keep the collections completely up-to-date: you may find deleted objects in the collections. (These deleted objects have names starting with "~TMPCLP".) You probably won't want to include these objects in the inventory, so the code that loops through the collections specifically excludes objects with names that start with "~TMPCLP". To determine which objects are deleted, the code calls the IsTemp function, as shown in the following code fragment:

For Each doc In con.Documents
   If Not IsTemp(doc.Name) Then
   ...
   End If
Next doc

Private Function IsTemp(ByVal strName As String)
   IsTemp = Left(strName, 7) = "~TMPCLP"
End Function

4.2.3.5 Comments

If you want to remove system objects from your inventory, you'll need to check each object and, if it's a system object, skip it in the display. You can use an object's Attributes property to see if it's a system object. See Access's online help for more information.

You might wonder why this application uses the Access containers to retrieve information about tables and queries, since this requires more effort than if the code had just used the TableDefs and QueryDefs collections. It makes sense to use the containers because the TableDefs/QueryDefs collections don't contain information about the owners of the objects, one of the items of information this application is attempting to track.

You can also use the collections provided by Access, such as AllForms, AllReports, AllTables, which can be useful for gathering information on your objects. But these too lack ownership information, which is part of the Jet database engine's security system and therefore must be accessed using the Jet Containers and Documents collections. The AllForms and AllReports collections do contain additional useful information, however, including an IsLoaded property for each of the AccessObjects in the collections.

4.2.4 See Also

For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.