What’s in a Table?

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.

Your download file is called   Alderman_what_in_table.accdb

 

Sidebar: Exposing a Collection

Sometimes you want to stop everything and just rip through a collection to find all of the things it contains. The two snippets below can be inserted into sdaGetAttributes to expose names for all the items in a field’s Properties collection. You need to declare two new variables and set the value of ysnRunSpecProc as follows:

Dim lngK As Long

 Dim ysnRunSpecProc As Boolean

 

 ysnRunSpecProc = True

Now insert the following snippet anywhere within the For…Next loop that uses lngI:

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

The If…Then snippet will execute once and print a numbered list of all of the Properties in the collection. You might need to be creative with regard to when this snippet executes, since Access will sometimes neglect to include a property if it’s not being used.

Sidebar: Access, Jet, and ADO Properties

It’s easy to confuse Access with Jet. Access is a tool for creating database front ends. Jet is Microsoft’s desktop database that ships with Access, Visual Basic, and a number of other Microsoft products. To make Jet adaptable enough to work with a variety of development tools, many Jet objects have a Properties collection. The application that uses Jet can add new items to the Jet Properties collections (see Peter Vogel’s article “Buying Some Property” in the June 1996 issue, and Gary Warren King’s article “Properties Without Fear” in the September 1998 issue for more in-depth discussions). The Description property is an example of that kind of property. Access, as part of its table design tool, allows you to enter values for the built-in Jet properties. Access also allows you to enter some additional values, like Description, that Jet doesn’t support. When you enter a value for those additional properties, Access will add the appropriate property to Jet and store the value that you entered in that property.

ADO uses the same technique but goes in the opposite direction. While Jet allows applications to add properties to Jet objects, ADO allows the data provider to add properties to the various ADO objects. Since ADO can work with a variety of data sources, Microsoft had to provide some mechanism for accessing features not common to all data sources. In ADO, each data provider can add the properties that it needs to the Property collections of the ADO objects.
 

Other Pages on The Site You May Like To Have a Look At

 Drilling with Combo Boxes

This entry was posted in Design and Tables. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.