Access Cookbook - Ken Getz [85]
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
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
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