Managing Tables

What is a Table?

I’m currently developing an application that imports several files from mainframe sources at the State of Nevada. The application uses a number of tables in a local Access database to clean up and normalize the data before it’s transferred to a SQL Server database. I’d been using a very good commercial tool to document my Access database designs, but at the time I needed the tool, the version for Access 2000 wasn’t available.

The documentation that I usually produce for an Access project consists primarily of a list of table attributes and a description of the relationships between the tables in my applications. I usually use a screenshot of the Access Relationships diagram and a limited listing of attributes by table to document my database (see Table 1 for a typical attributes listing for a single table).

Table 1. Example of documentation produced for the USystblDoc table.

Objects and system tables

the best way I’ve found to visualize an Access .mdb file is to think of it as a container holding all of the objects necessary to build a database and/or an application. If you’ve ever poked around in Access, you’ll find that a great deal of information about the tables, queries, and other objects that you create is contained in a set of hidden tables. Objects like TableDefs and QueryDefs, as their names imply, are just definitions or blueprints of tables and queries. When you’re using Design View for a table, you’re actually updating those system tables. The table that you’re creating doesn’t really exist until you start populating it with data (and do so according to the rules contained in your TableDef). A QueryDef is really nothing but a definition stored in a system table, which is then used to generate a recordset.

To view those hidden tables, select the View tab from the Tools | Options menu choice and put a check mark in the System Objects checkbox. The tables will then appear in your database window. The names of the hidden tables are prefixed with the letters „MSys“ (you can use the prefix „USys“ to name tables that you want to keep hidden from casual users). Depending on your version of Access, you might lack the permission to view some of these tables, and you’ll find that some of the information in the tables has been encrypted. The structure, layout, and meaning of the information in these tables can be difficult to determine.

Rather than work with those tables, however, Access provides a set of objects that give you the ability to extract whatever information you want. These objects include the TableDefs, QueryDefs, and Recordset objects. Each of these objects may give you access to other objects through their properties and collections (for example, the Fields collection in a TableDef object provides access to the Field objects for the a table). Collections, like Fields, are objects in and of themselves, with their own properties (such as Count) and collections (such as Properties). There are so many places to look up information that the job of finding the specific data in which you’re interested might appear overwhelming. Furthermore, you’ll find that much of the information that appears in one collection seems largely duplicated in another, leading you to ask which data is correct. The answer is, „All of it.“

Each successive version of Access seems to rearrange where internal data is stored and which objects have access to which pieces of information. You can normally count on upward compatibility to preserve the code that you create to use these objects. For instance, the original version of my documenter was written for Access 2000 and wouldn’t work for Access 97. The version that I rewrote for Access 97, however, worked just fine under Access 2000.

Extracting information

Before I show you how I drill down to get the information I want, let’s look at two routines I use that help my main documentation procedure. My first routine is used as part of a procedure to remove all of the data that was generated in an earlier run of the documenter:
Private Function DelTblData(strName As String) _
As Boolean
Dim DB As Database
Dim QD As QueryDef
Dim strSQL As String
Set DB = CurrentDb
Set QD = DB.CreateQueryDef("")
strSQL = "DELETE " & strName & ".* "
strSQL = strSQL & "FROM " & strName & ";"
DelTblData = True

Set QD = Nothing
Set DB = Nothing
Exit Function
End Function

As an example, the Field object has a property called Type that returns an integer indicating the data type of the field. GetType converts that value into a name that I can print in my documentation:
Dim fld As Field

strDataName = GetType(fld.Type)

Neither of my private procedures has an error handler. Since private procedures can be called only by other procedures within the module, any errors these routines generate will end up being passed back to the calling procedure. My main documenter procedure, sdaGetAttributes, calls all of the other routines and performs all error handling.

The sdaGetAttributes routine first uses DelTblData to clean out the documentation table USystblDoc. The routine then opens a recordset on my documentation table, opens a transaction, and iterates through the TableDefs collection, retrieving the TableDef objects in the collection. The transaction will buffer output until I issue a WS.CommitTrans, when the data will actually be commited to disk. Although the documentation for a typical database is often a small amount of data, the use of transactions can dramatically speed up processing by reducing the number of disk writes required. Here’s that opening code:

Sub sdaGetAttributes()

On Error GoTo sdaGetAttributes_Err
Dim WS As Workspace
Dim DB As Database
Dim TD As TableDef
Dim RSOut As DAO.Recordset
Dim lngI As Long
Call DelTblData(„USystblDoc“)

Set WS = DBEngine.Workspaces(0)
Set DB = WS.Databases(0)
With DB
For Each TD In .TableDefs

Once I’ve retrieved a TableDef from the collection, I check to see if the table name begins with the standard „tbl“ prefix. If it does, I use the TableDef’s Fields collection to retrieve each of the fields that make up the table definition. Having retrieved the field, I move the properties in which I’m interested into my documentation table:

If Left(TD.Name, 3) = "tbl" Then

For lngI = 0 To TD.Fields.Count – 1


RSOut(„TableName“) = TD.Name

RSOut(„FieldName“) = _


RSOut(„FieldType“) = _


RSOut(„FieldSize“) = _


RSOut(„FieldReqd“) = _


RSOut(„FieldDflt“) = _


On Error Resume Next

RSOut(„FieldDescr“) = _

TD.Fields(lngI).Properties _


On Error GoTo sdaGetAttributes_Err


Next lngI

End If

Next TD

End With

After updating my table, I commit the transaction to save all of my updates and close the recordset that I opened on my documentation table. What follows is the cleanup for the routine and the error handler.




On Error GoTo 0

Set RSOut = Nothing

Set TD = Nothing

Set DB = Nothing

Set WS = Nothing

Exit Sub


Select Case Err

Case 3034, 91

Resume Next

Case Else


MsgBox Err & „> “ & Error & _

“ (sdaGetAttributes/basDocumenter“

End Select

Resume sdaGetAttributes_Exit

End Sub

For most of the data that I want to use in my documentation, it’s a fairly simple matter to select an appropriate property and assign its value to an attribute in the output table. TD.Name retrieves the name of the table, for instance. TD.Fields(lngI). Properties lets me retrieve the properties for each field as I iterate through the loop, incrementing the variable lngI. The Type property returns an integer, and I use my GetType function to return a more readable string value.

The hardest property to retrieve is the Description. For this property, you have to go to the Properties collection kept for each field. If there was no value assigned to the Description value when the table was designed, Access won’t create a Description property. Attempting to access this property when it’s not there causes an error. To get around this, I just suspend error trapping for the line that retrieves the Description property, and everything works just fine.

Filtering the output

To produce the final documentation, I use a query to produce data for one table at a time. The SQL for the query is:

SELECT USystblDoc.FieldName AS Attribute,

USystblDoc.FieldType AS Type,

USystblDoc.FieldSize AS [Size],

USystblDoc.FieldDflt AS [Default],

USystblDoc.FieldReqd AS Required,

USystblDoc.FieldDescr AS Description

FROM USystblDoc

WHERE USystblDoc.TableName = [Enter Table Name:];

This query will prompt you for a table name and display data for that table only. To select the query’s result set for pasting into Word, just click the square in the upper left corner of the grid and press Ctrl-C. The data can then be pasted into Word with the headers intact. To select another table for viewing, you can press Shift-F9 and Access will rerun the query.

Even after using Access for years, I never cease to be amazed at its design and the scope of its capabilities. The opportunity to get under the hood and poke around at how Access organizes and exposes its architecture gives me a better understanding and helps me write better code. I hope this article will help you understand more of how Access works and provide a few tips to help your coding efforts see

Print Friendly, PDF & Email
Cookie Consent mit Real Cookie Banner