How To Document A Database: Using Microsoft Access Tools And Your Disciplines

How To Document A Database: Using Microsoft Access Tools And Your Disciplines

Oh the paperwork! Having spent a lot of time designing, developing and implementing your MS Access database you really should consider the housekeeping – so how to document a database?

You may feel that this task could be avoided altogether but what happens if:

  1. You are no longer wanted and someone else inherits your baby?

  2. You become all too important (promotion) and someone else needs to take over the responsibility?

  3. The business has grown and you don’t have the time to extend your database functionality?

  4. You need to provide technical and end-user training?

Without some sort of sensible database documentation, you and others are left with the potential exposure of database misuse, unstructured administration, a fragmented system and a lack of disciplines in carrying out essential repairs and tasks.

How To Document A Database

Therefore, a database design document will pay you dividends in the future when revisiting your system, working ‘under the hood‘ to carry out the essentials or adding new functionality.

How To Document A Database: Using Microsoft Access Tools And Your Disciplines

So what are your options?

Within Microsoft Access you have a set of database tools at your disposal. They include:

The Relationship Window showing which tables and queries are permanently joined to form a relational database which also has the ability to print this as a report too.

The Object Dependencies Window Pane which provides a collection of different objects stored together and in some cases are related to each other to emulate the logical workflows that end users simply take for granted.

The Database Document tool that runs via an Access report is based on selected objects and parameters which can be very detailed indeed. This can be run at any time and really should be the last report generated as it is a static report and will not update if changes have been made.

To run this report, locate the Database Tools tab from the ribbon bar and under the section group ‘Analyze‘ choose ‘Database Documenter‘. Later versions (pre 2007), locate the Tools menu and the ‘Analyze‘ sub menu provides the same utility.

MS Access Database Documenter

You can optionally export this report to Microsoft Word and save it as a separate document but be aware the larger the database, the more objects selected with the additional options, the larger the number of pages your report will be. Expect to see over 100 pages for a medium sized database!

Over and above these tools within Microsoft Access, we should also invoke some best practices and make notes as we plan, design and implement your Access database which is also part of how to document a database.

Here’s a quick simple checklist of some of the actions and steps to include to help build both your end-user and technical documents:

  1. Naming Conventions – All your objects (that means tables, queries, forms, report, macros and other database members) should follow a standard convention. Without planning ahead here, you end up with inconsistencies, the lack of coherence between object types and being too cryptic with related items. 

    Learn to use a prefix to identify a type followed by a meaningful name keeping it short. For example, a table of customers might be referenced as tbl_Customers or a query showing all UK orders for 2013 might be named as qry_UKOrders2013.

    Also note how I used the plural word for ‘Customers‘ and ‘Orders‘ as this makes up a collection. How you adapt your convention is down to you but this will provide an easy way to audit and maintain a database.This can also extend to field names too and include data types to your tables or even for the Access VBA code. For example, a field called ‘First Name‘ which is of course a Text data type might be officially named as ‘txt_FName‘ or a monetary amount to an order might be known as ‘cur_Amount‘.

    By the way, there is a property called ‘Caption‘ that can use a proper label for your fields so that end-users will only see a normal field name but still retain the official ‘under the hood‘ name.

  2. Primary and Foreign Keys – Every table should really have at least a primary key field (normally the first field) to a table being its unique identifier. In the comments field for your table designs, I annotate it with a PK (Primary key) or FK (Foreign Key) in addition to having a field name with prefix of  ‘ID‘ included like for instance ‘CustID‘ or ‘OrdID‘.

    This will help to identify which fields have been indexed and how they relate to other tables or queries and of course must not forget how important data integrity is for your database.

  3. Table Relationships – Have a map of related tables and queries as a guide to creating additional queries and other related objects. This can be generated via the Relationship Window interface where you have the option to create an Access report or save as a snapshot.

    This will also form the basis for your functional specification report (if required) and certainly help database administrators have a handle on the data model designed.

  4. Adding Comments To Objects– In Access, you have the database object properties feature where users can store comments. It is hidden by default but can be easily seen by simply right-mouse clicking the desired object and choose either the ‘View Properties’, ‘Object Properties’  or ‘Table Properties’ command (based on type).
  5. Adding Comments To Fields – Each field for your tables also have a ‘Comments‘ property which doubles up as a prompt for end-users showing up on the status bar but can easily be adapted as narrative for developers.Your forms and reports can control whether you want to use the status bar and display the narrative but in essence this is adopted for developers as notes.

  6. Adding Comments to VBA Code – REM the line which mean ‘Remark’ is a VBA command to ignore that line when running code. You can also use the more commonly used apostrophe (‘) which will do the same and when used effectively in your modules can really help other programmers.

    It can therefore be used to explain a procedure, expand on a line of code and temporarily disable a line of code without deleting it.

  7. Creating A User Guide – End-users will need a manual to explain functionality, concepts and business workflows. It can be a quick reference document or a training manual and contain plenty of illustration.

    This can be split into two parts with the first for general users and the latter for the more advanced or technical user. Either way, this puts the finishing touches to a documentation process that anyone inheriting a database can pick and use with very little formal training.

Most of the above could be part of the original database design specification document that can be either a detailed or as an outlined format (depending on who will benefit and the size of the database).

My functional specification documents includes and introduction, the current process (if any), the scope, the objectives, the proposed development, schemas (table, field and data type definitions), workflow and logical flow charts, user interface functionality, general notes and a proposed schedule.

All this could easily exceed 100+ pages but it provides a blueprint for all parties including developers, project managers, decision makers and end-users and is also the basis for your final release documents.

So hopefully you have some more insight into how to document a database and the standard tools Microsoft Access provides. Yes, it will require a little time to be invested here but the dividends it pays will be worth it.

Do you need help in putting together either a database proposal document or build an end-user guide? If so, drop me an email via my contact page.

One Reply to “How To Document A Database: Using Microsoft Access Tools And Your Disciplines”

  1. Wow, amazing weblog format! How long havee you been running a blog for?
    you make running a blog look easy. The full look of yyour web site is fantastic,
    as smartly as thhe content!

Leave a Reply

Your email address will not be published. Required fields are marked *