Stu Alderman explores the structure of your Access database and discusses the relationship between Access objects and System tables.
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 from FMS, but at the time I needed the tool, the version for Access 2000 wasn’t available. The result looks like this … read on
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.
Attribute | Type | Size | Default | Reqd | Description |
TableName | Text | 64 | Yes | ||
FieldName | Text | 64 | Yes | ||
FieldType | Text | 16 | Yes | ||
FieldSize | Text | 8 | Yes | ||
FieldReqd | Boolean | 1 | No | No | |
FieldDflt | Text | 64 | No | ||
FieldDescr | Text | 255 | No | ||
RecordID | Long | 4 | GenUniqueID() | No | Random Number Primary Key |
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 & ";" QD.SQL = strSQL QD.Execute (DB_FAILONERROR) DelTblData = True DeleteTableData_Exit: Set QD = Nothing Set DB = Nothing Exit Function End Function
The DeleteTableData function removes all data from the table whose name is passed in the strName argument (as long as no rows are locked and no integrity rules are violated). DelTblData is a general-purpose procedure that appears in most of my applications. I’ve found that Visual Basic doesn’t always destroy (or release memory for) object variables when they go out of scope. So, the DelTblData function sets all of its local object variables to Nothing before exiting the procedure. This habit makes sure that my modules run cleanly.
A second procedure is the GetType function. Passed a value that represents a data type for a field in the database, this routine converts that value into a name:
Private Function GetType(lType As Long) As String Select Case lType Case dbBigInt GetType = "BigInt" Case dbBinary GetType = "Binary" Case dbBoolean GetType = "Boolean" 'handle other data types Case Else: GetType = "Undefined" End Select 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) WS.BeginTrans 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.AddNew RSOut("TableName") = TD.Name RSOut("FieldName") = _ TD.Fields(lngI).Name RSOut("FieldType") = _ GetType(TD.Fields(lngI).Type) RSOut("FieldSize") = _ CStr(TD.Fields(lngI).Size) RSOut("FieldReqd") = _ TD.Fields(lngI).Required RSOut("FieldDflt") = _ TD.Fields(lngI).DefaultValue On Error Resume Next RSOut("FieldDescr") = _ TD.Fields(lngI).Properties _ ("Description") On Error GoTo sdaGetAttributes_Err RSOut.Update 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.
WS.CommitTrans RSOut.Close sdaGetAttributes_Exit: On Error GoTo 0 Set RSOut = Nothing Set TD = Nothing Set DB = Nothing Set WS = Nothing Exit Sub sdaGetAttributes_Err: Select Case Err Case 3034, 91 Resume Next Case Else WS.Rollback 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.
Sidebar: Exposing a Collection
Dim lngK As Long Dim ysnRunSpecProc As Boolean ysnRunSpecProc = True
If ysnRunSpecProc Then For lngK = 0 To RSIn.Fields _ (lngI).Properties.Count - 1 Debug.Print lngK, RSIn.Fields _ (lngI).Properties(lngK).Name Next lngK ysnRunSpecProc = False End If