Documenting your database – you know you should!

Part 1

Whenever I create a new database, it’s a bit like starting a new year. I start off with the best of intentions, planning lots of ways in which I’ll do things better than last time.

It used to be commenting – I'll make sure that my code is documented properly this time. And these days, by and large I’ve got that nailed. How did that happen? Through a mixture of bad experiences and good tools.

The bad experiences? It doesn’t take too many occasions of having to debug your own code that you wrote 6 months ago (let alone 6 years ago) before you really wish you’d written better comments. The procedure that started off as being so clear and concise that it would always be obvious what it did gradually morphs over the months into an amended, adjusted, twisted, late-night-affected and, of course, improved piece of code.

Improved, that is, in every way except readability. And because it was once so crystal clear, you never bothered to comment the code. That means that now you’re left debugging code that hovers uneasily between genius and gibberish…

The good tools? Well, like a lot of developers, I took the decision some while ago to invest in the FMS Utter Access suite. Because they have a good website the tool looked good and because I’m a sucker for buying shiny new things, I bought it.

But whereas my breadmaker languishes in a long-forgotten cupboard, some parts of the Access suite (I’ve not yet used them all) have really been a major bonus. Far and away the biggest aid to my productivity is the utility called the “Total Visual Code Tools” which, among much other wizardry, lets you create a procedure – Sub, Function or Property – with a single click. And this includes all the initial lines that I only used to put in when feeling virtuous, such as comments detailing the pupose of the procedure, its parameters, return values and suchlike. Along with this, enabling error handling to whatever standards you choose to adopt is taken care of, as is the error handling code itself. Take my advice and check it out.

Comments alone are not enough

while commenting your code really will help to save your sanity, there are other things you can do to document your Microsoft Access databases – before you ever touch the code editor.

For example, every table, query, form and report you create has a Properties sheet attached to it (just right-click on the object’s name in the navigation bar and select “Properties”). Here lies a Description property which allows you to document the purpose of the object as a whole.

And when you’re designing a table, as well as setting column data types and properties such as Caption, Field Size and Format, there’s the “Description” to the right of the column name and data type in the upper portion of the screen. (It should be remembered, incidentally, that unlike all the other descriptions and comments we’ve mentioned, this one is much more visible by default to your database users – it will appear in the status bar whenever a user edits data in the field).

It may sound like too much to be bothered with – I’m sure your memory is great and all – but when your database has somehow taken on a life of its own and grown to over 150 tables, each with 10 or a dozen columns or more… Well, you get the point.

Documenting a database for clients

The trouble is, I need more incentive sometimes to do what I know is right. And that incentive has come about in that I need to document database designs for clients – and potential clients.

When we are approached by a potential client to talk about developing a database, there’s typically a three stage process.

  1. Firstly, we’ll sit down and have a chat about exactly what sort of thing the client is after. This can take anything from 1 to 3 hours or so – it’s a chance for us to meet (if we don’t already know each other) and just to check that what the client needs sounds like it’s something that we could offer, and that the tools we use (Microsoft Access, SQL Server etc) are the right tools for the job. We do this for free.
  2. If that goes well, we’ll spend a few days (or sometimes more) working with the customer to put together a detailed proposal. This time is chargeable, as it’s time that we will need to have spent if we do go ahead with the database build.
    In that time, we’ll get to know exactly what’s needed, what processes are being supported and how the database should work, look and feel. We put together a significant document detailing exactly what we will and won’t include in the final database. This information includes the time we estimate it will take to build the database, and the costs involved.
  3. If that document meets the client’s requirements, we go ahead and build the database.

It’s important that as part of step two, our clients are able to see exactly what we will and what we won’t be including. Over the years, I’ve used various tools to create the diagram of the database, and to list tables, fields, data types and suchlike… But I always come back to doing it in Microsoft Access. Why wouldn’t I? Access is perfect for the job – and if the client goes ahead, part of the design work is already done.

But the issue then remains of getting the information from Access to Word, so that it can be put into a “customer-friendly” format.

Access does, of course, have its Database Documenter tool. But if you’ve ever used that, you’ll know that it’s far from perfect when it comes to producing nicely formatted information, and although you can control which elements of the design you output, ultimately you’ll still spend a lot of time copying, pasting, re-copying and pasting and then formatting.

A solution for exporting table designs to Word

So it is that we set to work to create a better solution, which is detailed below.

Tables, columns and descriptions

A sample table design output from Microsoft Access to Word

We started from the question “What do we want to export?”

The answer was that we wanted to produce a Word table for every table in our database. The first column should show the actual column name, the second the alias by which our client will know that column, the third indicates the data type, and the fourth has a description about the purpose of that column.

Above each table, we decided that we’d like a Word heading, showing the table name, and then below that a summary of the purpose of the table. This would be drawn from the description of the table, accesible by right-clicking on the table and selecting Properties.

The table description appears just above the list of columns and the table name is converted into a heading

Of course, there’s a lot more that needs to go into our proposal, but a relatively simple bit of code that loops through each of our tables, exporting table name to a Word heading, each table description to a short paragraph below the heading, and then creates a nicely formatted table listing all the columns, their captions, data types and descriptions makes a good start. And, of course, knowing that it will help to create our client proposal is a great incentive for us to keep these documentation properties up to date.

The code

The starting point for our code is a loop that looks at each table in our database in turn, excluding the system tables whose names are prefixed by MSys. For each table, we call a routine that will do the grunt work, and then output to the Immediate Window that we’re looking at this particular table:

    Dim tdf As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb()
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) <> "_" Then
            Debug.Print "Now outputting " & tdf.Name
            Call OutputDetails(tdf)
        End If
    Next tdf

In order to make this a little more useful, I thought it would be helpful to be able to output the design of a single table, or all the tables. Furthermore, having output the information to Word, we need to make the document visible, and I wanted to be able to work with Word from more than one procedure, so declared this as a Module-level variable.

Here’s the full procedure, along with the relevant module-level declarations. Note that because I’m going to use late binding, I won’t have a reference to Word, so I’m includnig declarations of constants to match those constants that are part of the Word object model:

    
Private wdApp As Object
Private wdDoc As Object

Private Const wdStory As Integer = 6

Private Const wdStyleTableLightListAccent1 As Integer = -174

Public Sub OutputTableDesignsToWord(Optional TableName As String

    ' Comments: Loops through all tables, and outputs them to Word
    '   If a single TableName is provided, outputs that table only
    ' Params  : TableName
    ' Modified:

    Dim tdf As DAO.TableDef
    Dim db As DAO.Database
    Set db = CurrentDb()
  
    If TableName = vbNullString Then
         For Each tdf In CurrentDb.TableDefs
              If Left(tdf.Name, 4) <> "MSys" And Left(tdf.Name, 1) _
                  <> "_" Then
                    Debug.Print "Now outputting " & tdf.Name
                    Call OutputDetails(tdf)
                End If
            Next tdf

        Else 'Have tablename - just get this one
            Set tdf = db.TableDefs(TableName)
            Call OutputDetails(tdf)
        End If

        Set tdf = Nothing
        Set wdDoc = Nothing
        wdApp.Visible = True
    End Sub

So, the core of this code is obviously the call to the OutputDetails procedure. Let’s take a look at that.

The procedure accepts one parameter – a reference to the TableDef to be documented in Word. Variables are created which will allow us to refer to a DAO field, to allow us to loop through each field in that table and a DAO property, so that we can loop through the required properties of each table and column in that table. Further variables are declared which will allow us to work with the Word Table object (as mentioned above, late bound in case the PC being used has an unexpected version of Word installed) and a simple string variable for working with the text descriptions.

The first action is then to call the GetWordDoc procedure (described later) which creates a Word document, if one doesn’t already exist:

    Sub OutputDetails(td As DAO.TableDef)
    ' Comments: Outputs all the required
    '           information on the current table to Word
    ' Params  : td - TableDef to be exported
        ' Modified:

        Dim fld As DAO.Field
        Dim prp As DAO.Property

        Dim tbl As Object ' Word table object
        Dim strDesc As String

        'Create Word doc if we've not already got one
        If wdDoc Is Nothing Then
            Set wdDoc = GetWordDoc
        End If
     ......
    End Sub

The next part of the procedure combines outputting the name of our table (a reference to which is held in the td variable) and its description along with some basic formatting. The table’s description is accessed by using the TableDef’s Properties collection and passing it the “Description” parameter.

Moving to the end of the existing content in the document is achieved by using Selection.EndKey, supplying the wdStory constant as the parameter for the Unit, meaning “go to the end of the document.” Remember that wdStory is a Word constant, so as we’re using late binding rather than a reference to Word, again, we’ve had to set up our own constant to make this work.

         'Go to end of doc
         With wdApp.Selection
             .EndKey unit:=wdStory

             'Add a couple of line breaks
             .TypeParagraph
             .TypeParagraph

             'Now add the table name and its table-level description
             .Text = "Table: " & td.Name
             .Style = "Heading 2" 'One of the built-in Word styles
             .EndKey unit:=wdStory

             .TypeParagraph

             .Text = td.Properties("Description")
             .EndKey unit:=wdStory
        End with

The next task is to create a table, containing four columns, with the Description property for each column inserted as one of the columns.

We’ll pick that task up in Part two of this article, which we’ll publish very shortly, along with a sample of all the code used here for you to download.

If you’ve found what we’ve covered so far useful, let us know by adding your thoughts to the comment box below. Similarly, if you’ve got ideas about what else you’d find handy – let us know that too.