Building an Access Control Panel

Ivor Davies

Here's a set of utilities that lets you organize online access to information about your database.

A few months ago, I decided to see whether I could improve upon what Microsoft has built into Access for documenting my work in progress. The aim was to help my colleagues and me reduce the amount of time we were spending referring back and forth between tables, queries, and the Relationship window. We also wanted to create a descriptive label for every object in Access that has an interface -- queries, tables, forms, and reports. We wanted the equivalent of an electronic sticky note that we could summon and dismiss at will (the last thing that any of us wanted was for our development space to become as cluttered as our workspace).

This two-part series describes the utility that resulted. The complete utility is available in the accompanying Download file. It consists of a set of objects that can be easily imported into any new project and requires no customization beyond making sure that the Microsoft Office 8.00 Object Library is referenced by your project (I use the Object Library to create a toolbar). Once imported into your project, the utility can be used to do the following:


Each object in the utility has its name prefixed with the letter "z" so that they'll sink to the bottom of your object listings. This also lets you easily scoop them up and delete them once your project is ready for delivery. But as you'll see in Part 2, there are some good reasons for retaining these objects, as they can provide your users with useful information about the forms and reports that make up their application.

This article will describe the utility that gathers the information about your application and allows you to examine that information. In Part 2, I'll describe the procedures I developed for setting and retrieving the object descriptions using the Documents Collection, among other cool utilities.

Relational meta-data
One of its greatest strengths and most appealing features for mission-critical work is that Access closely follows the rules of Dr. Edgar Codd, the father of the relational database. Dr. Codd's famous 13 rules (numbered from 0 to 12, just to confuse the uninitiated) were first published in 1985 and are the de facto standard for all database applications that describe themselves as "relational." I believe that Access observes them more fully than any other desktop product. Rule 4 requires that the "meta-data" -- the database's own descriptions -- be stored in the same way as the regular data (that is, using tables). That's the role of the MSys tables, and they play an essential role in observing Dr. Codd's Rule 10.

Rule 10 requires that business rules and data integrity restraints also be stored in the Data Dictionary. Most so-called relational databases ignore this requirement and store these rules within the application's language, in file headers, or even in a separate file (which leaves them open to accidental or deliberate damage). If your data is maliciously damaged but your business rules remain intact, then your integrity rules will reveal the mischief sooner rather than later. But if your integrity rules are changed, especially by someone who knows how to hide the damage, your entire information system could gradually become corrupt. Data integrity rules need to be treated like the king on a chessboard -- hidden behind the full set of your security measures, not left in the front line like a disposable pawn.

I mention this because I need to issue a word of warning about these utilities. These techniques rely heavily on using the MSys table objects that Access uses to store data about your database. Microsoft's policy on the MSys objects is that they aren't supported and might be changed in future releases. Still, having implemented Access's meta-data in a secure data dictionary, I really can't see the Access development team undoing or so radically changing their policy that I'd have to abandon these utilities. However, it would be unfair of me not to give you this "caveat developer."

Building the utility
The utility requires four tables, three of which store dynamic data used by the utility's various routines. The fourth is a lookup table that holds field types and the numbers that Access uses when recording information about fields in the MSys tables (see Table 1). I haven't been able to discover whether the numbers 13 and 14 are in use, and I've yet to come across either of them in any database. If you've found them, or if you know what they are, please contact me.

Field number Field type
1 Boolean
2 Byte
3 Integer
4 Number (Long)
5 Currency
6 Single
7 Double
8 Date\Time
9 Binary
10 Text
11 OLE Object
12 Hyperlink\Memo
15 Replication ID

Table 1. The Access field types.

The first use of the MSys tables is to build a stored query that returns a list of all the tables in the database. It does this by reading the MSysObjects table and retrieving only the type 1 records (As Table 1 shows, type 1 is what Access uses to refer to tables internally):

Select *
From MsysObjects
Where Type = 1


This query is used to populate the combo box on the form zfrmTblData, plus two text boxes. A variation on the query appears in zqryTblInfo2, which uses this DLookup function and the lookup table from Table 1 to assign object names to MSysObject records:

DLookUp("[FieldText]","ztblFieldTypes","
     [fieldnumber] = " & [type])


Getting the data on all the queries in the database is done the same way by retrieving the type 5 records, which is Access's internal reference for queries.

This next query (zqrySelectReports) selects all of the reports in the database:

Select *
From MsysObjects
Where Type = -32764
And Left([name],1) <> "s"


The expression "Left([name],1) <> "s"" is used to filter out subreports. This depends on my naming convention, which prefixes subreport names with "srpt". In Part 2 of this series, one of the goodies that I'll be presenting is a comprehensive report command center that uses this report list. Since I didn't want users opening orphaned subreports, I set up the query to ignore all of the subreports in a database. The type parameter of "-32764" is Access's internal code for reports.

I could have used the same technique to select forms (the type value for forms is -32768). I could even have excluded subforms, as I also begin their names with "s". Instead, I used DAO and the Forms collection to load the table ztblFormInfo with information about all the parent forms in the project:

Public Sub FormInfo()
Dim dbs As Database
Dim rst As Recordset
Dim varFrmId As Variant
Dim VarDoc As Document
Dim strSQLDelete As String

On Error GoTo errFI
 
Set dbs = CurrentDb
Set varFrmId = dbs.Containers!Forms
Set rst = dbs.OpenRecordset("ztblFormInfo", 
   dbOpenDynaset)
'deletes the existing information in table
strSQLDelete = "DELETE * " & _
  "FROM ztblFormInfo"
dbs.Execute strSQLDelete
'writes the new information to the recordset
For Each VarDoc In varFrmId.Documents
  With rst
   .AddNew
   !FormName = VarDoc.Name
   .update
End With
Next
rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub


This procedure is called by the load event of zfrmFormInfo and populates the form's list box with all the forms in the database. The Delete query ensures that old information from previous projects isn't inadvertently left in the tables.

I now need just one more query, to return the individual expressions for the query we select for analysis. You can see the QBE grid for the query that does this in Figure 1. Each query is identified by its ID number, which is common to the MSysObjects and MSysQueries tables.

The forms
With these queries and tables in place, I can now construct the forms to list the information that interests me.

In Figure 2, you can see the zfrmTblInfo where the information about tables is assembled. The list of relations is obtained from the Relations Collection using the following code:

For Each relRelate In dbs.Relations

dbs.Execute strSQLDelete
For Each relRelate In dbs.Relations
    With rst
     .AddNew
     !primarytable = relRelate.Table
     !primaryfield = relRelate.Fields(0).Name
     !ForeignTable = relRelate.ForeignTable
     !foreignfield = relRelate.Fields(0).ForeignName
     .update
   End With
 Next


The full procedure used for this is called Relations, and you'll find it in basUtilities in the sample database. The information about the fields comes from two procedures, also in basUtilities. FieldInfo uses DAO and the Fields collection to return the fields for the table selected on the form:

Public Sub FieldInfo()
'uses DAO to extract list of field names and types

On Error GoTo TblErrHandle

Dim dbs As Database
Dim rst As Recordset
Dim strSQLDelete As String
Dim dteCreate As Date
Dim dteUpdate As Date

Set dbs = CurrentDb

'SQL string to clear table
strSQLDelete = "DELETE * " & "FROM ztblTblInfo"
'clears table of contents
dbs.Execute strSQLDelete
'creates recordset
Set rst = dbs.OpenRecordset("ztblTblInfo", _
    dbOpenDynaset)

'selects table name from combo on frmTableData
mpstrTblName = Forms!zfrmTableData!cboListTbl.Value

'extracts createdate and updates and fills text boxes
dteCreate = dbs(mpstrTblName).DateCreated
dteUpdate = dbs(mpstrTblName).LastUpdated
Forms!zfrmTableData!txtCreateDate.Value = dteCreate
Forms!zfrmTableData!txtLastUpdate.Value = dteUpdate

'sets the value of the variable to include field and type
 For Each mpfdFldDef In _
    DBEngine(0)(0)(mpstrTblName).Fields
 'adds them to the recordset
   With rst
    .AddNew
    !FieldName = mpfdFldDef.Name
    !Type = mpfdFldDef.Type
    .update
   End With
 Next

End Sub


Having listed the fields, the next task is to create a list of each Field's properties:

Public Sub FieldDetails()
'uses DAO to list field values using the 
'field properties collection
'returns the values to a message box

On Error GoTo FldErrHandle

Dim varFldName As Variant
Dim strInfo As String
Dim PrpField As Property
Dim dbs As Database

Set dbs = CurrentDb
'retrieves the table name from frmTableData
mpstrTblName = Forms!zfrmTableData!cboListTbl.Value
'retrieves the field name
varFldName = Forms!zfrmTableData!lstFieldInfo

'returns values from the properties collection

For Each PrpField In dbs(mpstrTblName) _
   (varFldName).Properties
  strInfo = strInfo & PrpField.Name & "="
  strInfo = strInfo & PrpField.Type
  strInfo = strInfo & vbCr
Next

End Sub


By the way, another way to retrieve properties from the Properties Collection is:

For Each PrpField In _
   DBEngine(0)(0)(mpstrTblName)(varFldName).Properties


This makes use of the DAO object heirarchy by going through the DBEngine object (the first "(0)" retrieves the first Workspace object, and the second "(0)" retrieves the first database in that Workspace). This method would actually be slightly faster than the method I used. However, by using the following code, I forced a requery on the database information and so reflected the most up-to-date information in the database:

Set dbs = CurrentDb


If I went through the DBEngine object, Access would have relied on its internal records, which aren't updated until the Close event of the database fires.

Figure 3 shows the form for displaying information about queries. The list box is populated by the query I discussed earlier. The form displays the number of queries found, a value that's returned by:

Dim intQCount
intQCount = DBEngine(0)(0).QueryDefs.Count
Me!TxtCount.Value = intQCount



The information on the structure of the query was gathered with the following code:

For Each qdQueryList In dbs.QueryDefs
'find the query selected in the combo box
 If Forms!zfrmQryInf!cboQryNames.Value = _ 
      qdQueryList.Name Then
 'move properties for the query to variables
   strQryShow = qdQueryList.SQL
   strType = qdQueryList.Type
   dteCreate = qdQueryList.DateCreated
   dteUpdate = qdQueryList.LastUpdated


Building
With these tools in place, you can quickly and easily examine the main properties of the tables and queries that you create. From the forms provided, you can also run or open them for amendment, doing it all from one place. In Part 2, I'll add the procedures to create and return object descriptions (our yellow sticky notes), and I'll build the report launcher for end-users. This might not be a giant leap for Access developers, but it considerably cuts down the amount of time wasted searching for information during the development process. As H.G. Wells said, "In the kingdom of the blind, the one-eyed man is King".



Ivor Davies is the owner of Impro-Data Technologies, a software development company specializing in Bayesian, database, and business software based on Microsoft products. Ivor writes articles on science and technology, as well as on developing with Access, Office, and VB, and he welcomes correspondence on these subjects at ivordavies@dial.pipex.com or through his company's Web site at www.impro-data.com.