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?
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:
Import the form zsfrmInventory from 04-02.MDB into your own application.
Load and run the form. As it opens, it builds the object inventory, saving the data in zstblInventory. If you want to rebuild the inventory once the form's up, click the Rebuild Object Inventory button. This recreates the inventory table and fills it with information about all the objects in your database. Figure 4-4 shows the form once it's been run on a sample database.
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.
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
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
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
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
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
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.
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.