Navigation:  Articles > Sep-1998 >

Building an Access Control Panel

Previous pageReturn to chapter overviewNext page

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

See part 2 here An Access Control Panel, Part 2
 
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:

Review all the technical information for all queries and tables
Attach, edit, and delete a description for any interface object from anywhere within the database

 
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.
 
Table 1. The Access field types.

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

 
 
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.

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

199809_id2 Figure 2
 
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.

199809_id3 Figure 1
 
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".
 
Read about the download DASHBRD.ZIP on this page