Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. Import the form zsfrmInventory from

   04-02.MDB
   into your own application.

2. 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.

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
tableit 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.

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.