Documenting Access Databases

support@ascassociates.biz , Sales Enquiries

Documenting Access Databases

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.

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.

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.

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.

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.

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.

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

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz