Generating a Data Dictionary with the Database Documenter

After you've determined the individual data entities that make up the tables of your database and have established the relationships between them, the next step is to prepare a preliminary written description of the database, called a data dictionary. Data dictionaries are indispensable to database systems; an undocumented database system is almost impossible to administer and maintain properly. Errors and omissions in database design often are uncovered when you prepare the preliminary data dictionary.

When you've completed and tested your database design, you prepare the final detailed version of the data dictionary. As you add new forms and reports to applications or modify existing forms and reports, update the data dictionary to keep it current. Even if you're making a database for your personal use, a simplified version of a data dictionary pays many dividends on the time invested.

Access 2003's Database Documenter creates a report that details the objects and values of the properties of the objects in the current database. You can also use Office Links to export the report as a rich-text format (RTF) file (Publish It with Microsoft Word) or in Excel business interchange file format (BIFF) file (Analyze It with Microsoft Excel).

In many cases, Documenter tells you more than you want to know about your database; the full report for all objects in Northwind.mdb, for example, requires about 400 printed pages. Most often, you only want to document your tables and, perhaps, you queries to create a complete data dictionary. The following steps show you how to create a data dictionary for the table objects (only) of Northwind.mdb:

1. Open the database you want to document, Northwind.mdb for this example, and choose Tools, Analyze, Documenter to open Documenter's tabbed dialog.

2. Click the tab for the type of database object(s) you want to document. Current Database and Tables are the most common data dictionary objects, so click the Current Database Tab.

3. Mark the Properties and Relationships check boxes, or click Select All (see Figure 5.50), and click the Tables tab.

   Figure 5.50. Mark both the Properties and Relationships check boxes to list database properties and generate simple diagrams of table relationships.

   

4. Mark the check boxes of the tables to analyze, Employees and HRActions for this example (see Figure 5.51), and click the Options button to open the Print Table Definition dialog.

   Figure 5.51. Select a couple of tables to give the Documenter a test run.

   

5. Northwind.mdb isn't a secure database, so clear the Permissions by User and Group check box. The default options for fields and indexes specify the full gamut of information on these objects (see Figure 5.52). Click OK to close the dialog.

   Figure 5.52. The Print Table Definition dialog lets you specify the amount of detail the report contains for tables.

   

6. Click OK to close the Documenter dialog and start the report generation process. After a few seconds, page 1 of an 17-page report opens in Print Preview mode.

7. Click the Print Preview window to display the report at 100% scale and expand the size of the window to display the full width of the report (see Figure 5.53). For this example, the Employees table is the report's first object.

   Figure 5.53. Properties of tables and their fields appear at the beginning of Documenter's report.

   

8. Scroll the report to review the remainder of page 1, and then click the next page button to display additional fields of the Employees table and the HRActions table.

9. Database properties appear at the end of the report. Navigate to page 15 to view the first page of the Database section of the report. Pages 16 through 18 of the example report display simplified entity-relationship (E-R) diagrams for the database's relationships (see Figure 5.54).

   Figure 5.54. Documenter's Current Database, Relationships option generates Spartan entity-relationship diagrams for all tables in the database, regardless of the tables you select to document.

   

10. Click the Print button on the main toolbar to print the report, or click the OfficeLinks button to create a doc_rptObjects.rtf or doc_rptObjects.xls file in the My Documents folder. Word or Excel opens automatically to display the exported file.

11. Right-click the report and choose Send To, Mail Recipient (as Attachment) to open the Send dialog in which you can choose one of eight formats, including HTML, Report Snapshot, and XML. Alternatively, choose Export, to open the Export Report dialog, and save the file in one of the supported formats.

For more information on emailing or exporting reports, see "Mailing Report Snapshots," p. 696 and "Exporting Static Reports as XML," p. 965.

Documenting other types of objects in your database follows the same method outlined in the preceding steps. The ability to send Documenter reports by email simplifies keeping others involved in the database design project up to date on changes.