O'Reilly Book Excerpts: Access Cookbook, 2nd Edition
Cooking with Accessby Ken Getz, Paul Litwin and Andy Baron
Editor's note: The authors of Access Cookbook, 2nd Edition have selected four recipes offering practical solutions to common problems Access users face. Read on and learn how to perform a mail merge from Access to Word, create a web front end to an Access table, build an object inventory, and export columns to an HTML table.
Recipe 4.2: Build an Object Inventory
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.
TIP: 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.
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
code turns on the hourglass cursor and sets the main list
box's RowSource property to
(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,
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
Creating the inventory table
function prepares the zstblInventory table to hold the current
database's inventory. The code in
CreateTable first attempts to delete
zstblInventory (using the
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
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
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
TIP: 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
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,
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.