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.MDBinto 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;"
Me!cmdDocumentSelected.Enabled = False
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 Section 1.15.2
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 FunctionThe 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 = 0) On Error GoTo 0 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 strType = IIf(isTable(doc.Name), "Tables", "Queries") Else strType = strContainer End If
The value of
strType will be written to zstblInventory
along with the document information.
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:
With rst
.AddNew
!Container = strType
!Owner = doc.Owner
!Name = doc.Name
!DateCreated = doc.DateCreated
!LastUpdated = doc.LastUpdated
.Update
End WithThe 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.
Access 2000 added new collections (AllForms, AllReports, AllTables, etc.), 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 new AllForms and AllReports collections do contain additional useful information, however, including an IsLoaded property for each of the AccessObjects in the collections.
Get Access Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
