Why Document Your Database

The idea behind documentation is that it serves a purpose, and does not simply produce a wad of paper that nobody will ever look through. So documentation should be purposeful. Some points to consider as benefits in producing documentation are:-

• If a developer moves on then others can maintain the code.
• It helps you focus on your design and improve the quality of your work.
• It acts as a road map to assist you when you later return to enhance the application.
• It demonstrates a structured approach to constructing a system.

Access being a tool which can rapidly produce forms, queries, tables and reports, has a particular nasty habit of allowing the rapid accumulation of an uncontrolled mess. How often have you seem design objects called "Report For John" or "Report For Peter - can Be Deleted" or "zzzMyReport" sprinkled in a database.

Top of Page

Naming Your Design Objects

Your first defence against anarchy is to name things in a meaningful, if abbreviated form. Traditionally many developers have followed the conventions of Leszynski-ReddickNamingConvention , Which is a good starting point. But as applications can often have 80 or 90 queries in them, the convention does not address the real challenge of managing your forms and tables etc.. You can consider some of the following strategies:-

• Naming objects by association, tblCustomer, frmCustomer, frmCustomerSubformDetails, qryCustomer.
• Naming objects by similarity in function, qryImportCustomers, qryImportCountries.

The essential point here is to have at least some "method in you madness". More recent improvements in Access have focused on short-cut grouping of design objects to improve application management.

Top of Page

Buying A Documentation Tool

If you can, then purchasing a documentation tool is a sound investment, probably the most popular tool is the FMS TotalAccessAnalyzer. You could also use the built in documentation feature in access (Tools-Analyze-Documenter), although this is rather limited.

Top of Page

Writing A Documentation Tool

This is not as difficult a proposition as it sounds, all you really need are a couple of tables into which you will update the contents of the database against which you will make annotations. Then add a few reports and you will be well on your way to having a basic documentation tool.

Top of Page

Choosing Between DAO and ADOX or System Tables

Access gives you three alternative mechanism for establishing documentation. I would suggest that examining system tables such as MSysObjects is fairly limited and certainly cryptic to understand.

Selecting DAO (Data Access Objects) has the advantage that it is laid out in a form that fits the Access JET architecture, whilst selecting to use ADOX (Active Data Objects Extension) means that you could also use the tool against SQL Server or SQL Express.

Top of Page

Example Of DAO Documentation Code

If you are keen on writing your own documentation tool then you can download the following sample to help you get started :- DAO Documentation Code.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz