Back in the days of mainframes and very formal centralized Management Information System (MIS) departments, documentation was a mandatory requirement for the completion of an application. Today, it seems as though all types of people are developing applications: administrative assistants, CEOs, sales managers, MIS professionals, and so on. To make matters worse, many of us who consider ourselves MIS professionals never received any formal systems training. Finally, the demand to get an application up and running and then to move on to the next application is more prevalent than ever. As a result of all these factors, it seems that documentation has gone by the wayside.
Despite all the reasons why documentation doesn't seem to happen, it is as important to properly document your application today as it was in the mainframe days. Documentation provides you and your users with these benefits:
Fortunately, Access ships with an excellent tool to assist you with the process of documenting your database: the Database Documenter. Although this tool can be used without any special preparation on your part, a little bit of work as you build the components of your application can go a long way toward enhancing the value of the output supplied by the Database Documenter.
The Database Documenter prints all field and table descriptions entered in the design of a table. Figure 34.1 shows a table in Design view. Notice the descriptions for the ClientID and StateProvince fields. These descriptions provide additional information that is not obvious by looking at the field names. The Table Properties window also contains a Description property. This property is included in the documentation for the table when it is printed in the Database Documenter.
Figure 34.1. Documenting a table by including descriptions of each field and using the Table Properties box.
In addition to enhancing the output from the Database Documenter, entering a table description also assists you and the users of your database when working with the tables in the database. Figure 34.2 shows the Database window after descriptions are entered for the tables in the database. Notice that the description of each table appears in the Database window.
Just as you can enhance the output the Database Documenter provides for tables, you also can enhance the output the Database Documenter provides for queries. Figure 34.3 shows the Query Properties window. As you can see, the Description property is filled in with a detailed description of the purpose of the query. Figure 34.3 shows the description of a query, and Figure 34.4 shows the description of an individual column in a query. Both the query and field descriptions are included in the output provided by the Database Documenter.
Documentation is not limited to table and query objects. A form has a Description property. It cannot be accessed from the Design view of the form, though. To view or modify the Description property of a form, follow these steps:
Make the Database window the active window.
Right-click on the form for which you want to add a description.
Choose Properties. The Object Properties dialog box appears, as shown in Figure 34.5.
Enter a description in the Description text box.
Click OK. The description you entered appears in the Database window, as shown in Figure 34.6, and it also appears in the output from the Database Documenter.
Reports are documented in exactly the same manner as forms. Reports have a Description property that must be entered in the Object Properties dialog box. Remember that to access this dialog box, you right-click on the object in the Database window and then choose Properties.
Macros can be documented in significantly more detail than forms and reports. You can document each individual line of the macro, as shown in Figure 34.7. Not only does this provide documentation in the Database Documenter, but macro comments become code comments when you convert a macro to a Visual Basic module. In addition to documenting each line of a macro, you can add a description to the macro. As with forms and reports, to accomplish this, right-click on the macro from the Database window and choose Properties.
I cannot emphasize enough how important it is to document your modules. You accomplish this by using comments. Of course, not every line of code needs to be documented. I document all areas of my code that I feel are not self-explanatory. Comments help when I revisit the code to make modifications and enhancements. They also help anyone who is responsible for maintaining my code. Finally, comments provide the user with documentation about what my application is doing. Comments print with your code modules, as shown later in this chapter in the section “Using the Database Documenter.” As with the other objects, you can right-click on a module to assign a description to it.
In addition to enabling you to assign descriptions to the objects in the database, Microsoft Access enables you to document the database as a whole. You do this by filling in the information included in the Database Properties window. To access a database's properties, choose File | Database Properties or right-click the title bar of the Database window and choose Database Properties. The Database Properties dialog box appears, as shown in Figure 34.8. As you can see, it is a tabbed dialog box; tabs include General, Summary, Statistics, Contents, and Custom.
Descriptions of the tabs in the Database Properties dialog box follow:
General: The General tab displays general information about your database. This includes the date the database was created, when it was last modified, when it was last accessed, its location, its size, its MS-DOS name, and its file attributes. None of the information on the General tab is modifiable.
Summary: The Summary tab, shown in Figure 34.9, contains modifiable information that describes the database and what it does. This tab includes the database title, its subject, and comments about the database. It also includes the hyperlink base—a base address used for all relative hyperlinks inserted in the database. The base address can be an Internet address (URL) or a filename path (UNC).
Statistics: The Statistics tab contains statistics of the database, such as when it was created, last modified, and last accessed.
Contents: The Contents tab, shown in Figure 34.10, includes a list of all the objects contained in the database.
Custom: The Custom tab enables you to define custom properties associated with the database. This is useful when you are dealing with a large organization with numerous databases, and you want to be able to search for all the databases containing certain properties.
The Database Documenter is an elegant tool that is part of the Access application. It enables you to selectively produce varying levels of documentation for each of the objects in your database. Here's how it works:
Make sure that the Database window is the active window.
Choose Tools | Analyze | Documenter. The Documenter dialog box appears, as shown in Figure 34.11.
Click the appropriate tab to select the type of object you want to document. To document a table, for example, click the Tables tab.
Enable the checkbox to the left of each object that you want to document. You can click the Select All command button to select all objects of the selected type.
Click the Options button to refine the level of detail provided for each object. Depending on which object type is selected, different options are displayed. Database Documenter options are covered in the next section of this chapter.
Repeat steps 3 through 5 to select all database objects that you want to document.
Click OK when you are ready to produce the documentation.
Tip
To document all objects in the database, click the All Object Types tab and then click Select All.
Warning
Access can take quite a bit of time to produce the requested documentation, particularly if numerous objects are selected. For this reason, you should not begin the documentation process if you will soon need your computer to accomplish other tasks. This is because, while Access is processing this task, switching to another application becomes difficult if not impossible—how difficult depends on the amount of RAM installed on your system.
Note
To document the properties of the database or the relationships between the tables in the database, click the Current Database tab and select Properties or Relationships.
After you select all the desired objects and options and click OK, the Object Definition window appears. You can use this Print Preview window to view the documentation output for the objects you selected. (See Figure 34.12.) This Print Preview window is just like any other Print Preview window; you can view each page of the documentation and send the documentation to the printer.
By default, the Database Documenter outputs a huge volume of information for each selected object. Each control on a form is documented, for example, including every property of the control. It is easy to produce 50 pages of documentation for a couple of database objects.
Besides being a tremendous waste of paper, though, this volume of information is overwhelming to review. Fortunately, you can refine the level of detail provided by the Documenter for each category of object you are documenting. Just click the Options button in the Documenter dialog box.
Figure 34.13 shows the Table Definition options. Notice that you can specify whether you want to print table properties, relationships, and permissions by user and group. You also can indicate the level of detail you want to display for each field: nothing; names, data types, and sizes; or names, data types, sizes, and properties. For table indexes, you can opt to include nothing; names and fields; or names, fields, and properties.
Figure 34.13. You can use the Print Table Definition dialog box to designate which aspects of a table's definition are documented.
If you select the Queries tab and then click Options, the Print Query Definition dialog box appears, as shown in Figure 34.14. Here, you can select the level of detail to be output for the selected queries. You can choose whether to include properties, SQL, parameters, relationships, and permissions by user and group for the query. You also can select the level of detail to include for each column of the query and for the indexes involved in the query.
Figure 34.14. You use the Print Query Definition dialog box to designate which aspects of a query's definition are documented.
The Form and Report options are similar. Figure 34.15 shows the Print Form Definition dialog box. Here, you can specify whether you want to print properties, code, and permissions by user and group for the form. For each control on the form, you can choose to print nothing, the names of the controls, or the names and properties of the controls. The Print Report Definition dialog box offers the same options.
Figure 34.15. You use the Print Form Definition dialog box to designate which aspects of a form's definition are documented.
For macros, you can choose whether you want to print macro properties, actions and arguments, or permissions by user and group. For modules, you can choose to view properties, code, and permissions by user and group.
As you can see, the Database Documenter gives you great flexibility in the level of detail it provides. Of course, if you haven't filled in the properties of an object (for example, the description), it does you no good to ask the Documenter to print those properties.
After you produce the documentation and it appears in the Object Definition Print Preview window, you can output it to other formats. From the Print Preview window, choose File | Output To. The Output To dialog box appears, as shown in Figure 34.16. Notice that you can output the documentation to HTML, Microsoft Excel, a Rich Text Format file, or an MS-DOS text file. After you select the format and click OK, Access prompts you for a name for the new file. If you select the AutoStart checkbox from the Output To dialog box that follows the screen shown in Figure 34.16, the Documenter creates the file and then launches you into the appropriate application. The application launched depends on your Registry entries. If Microsoft's Internet Explorer (IE) is the application associated with the file extension HTML, for example, AutoStart launches IE with your Documenter output loaded when you output to an HTML file. Similarly, if you choose a Microsoft Excel file format and Excel is associated through the Registry with XLS file types, AutoStart launches Excel with your output loaded on file output. The same holds true for the other file types—RTF and TXT and their respective Registry associations, which usually are Word and Notepad.
Most of the time, the options provided by the Database Documenter are sufficient. At times, you won't like the format the Database Documenter selects—or, more important, you might want to document properties of the database objects not available through the user interface. In these situations, you can choose to enumerate the database objects by using code and then output them to a custom report format.
Using data access objects, you can enumerate any of the objects in your database. Listing 34.1 shows an example.
Example 34.1. Using DAOs to enumerate the table objects in a database.
Sub EnumerateTables()
Dim db As DATABASE
Dim tdf As TableDef
Dim fld As Field
Dim fSystem As Boolean
Dim fAttached As Boolean
Set db = CurrentDb
DoCmd.SetWarnings False
For Each tdf In db.TableDefs
fSystem = tdf.Attributes And dbSystemObject
fAttached = tdf.Attributes And dbAttachedTable
DoCmd.RunSQL "INSERT INTO tblTableDoc" _
& "(TableName, DateCreated, LastModified, " _
& "SystemObj, AttachedTable ) " _
& "Values (""" & tdf.Name & """, #" _
& tdf.DateCreated & "#, #" _
& tdf.LastUpdated & "#, " _
& fSystem & ", " & fAttached & ")"
Next tdf
DoCmd.SetWarnings True
End SubThe EnumerateTables routine, located in the basDocument module of CHAP34EX.MDB on your sample code CD-ROM, documents various information about the tables in the database. It uses a For..Each loop to loop through all the table definitions in the database. For each table in the TableDefs collection, EnumerateTables determines whether the table is a system table or a linked table. It then executes an SQL statement, inserting all the requested information about the table definition into a table called tblTableDoc. This table then can be used as the foundation for a report. Of course, when you use appropriate For..Each loops and properties, any information about any of the objects in the database can be obtained by using the same technique.
Practice using various options in the Database Documenter for the Time and Billing application. As you change the options for each object type, view the output differences. If you are particularly ambitious, try writing some DAO code to enumerate the objects of the database.
Documentation is a necessary part of the application-development process; fortunately, Microsoft Access makes it very easy. This chapter covered the object Description properties Access provides, as well as the extremely powerful Database Documenter. This chapter also highlighted how you can create your own documentation using data access objects and custom reports. By using any combination of the techniques covered in this chapter, you can produce very complete documentation for all aspects of your application.