Database Documentation Best Practices

[BigJoe9090]

I've been working on a Access database for last 20 years in my business, all self taught skills but I have zero database documentation. Having been self taught documentation never really came up.

It is an Access frontend with data stored in a local MySQL server and used by around 20 people.

There are around 4,000+ items (tables, linked tables, queries etc..) not to mention lots of VBA code.

I am now at the point where I want to start documenting the database for these reasons:

• I am finding that I am writing VBA functions and queries that already exist.
• I want to start removing some items from the database that are no-longer in use but not sure how everything is linked.
• I want to think about passing the database work over to somebody else.

So am I asking for some help.

Where should I start with documentation?

• Just start with a Word document and start writing?
• What about putting all database items into Excel and writing a description?
• 3rd Party tools?
• Database analyse tools?

How do people keep track of VBA functions/queries you have written in a database and have the ability to look them up?

Database documentation feels like it should be more than just a list of tables and queries with a short sentence explaining what they do, it feels like there should be more details stored on how things work and integrate.

I would really appreciate any help on where to start or links to best practices or resources.

Many thanks

BigJoe

 

[Josef P.]

There are some tools that will help you to list all the elements:
* https://www.isladogs.co.uk/database-analysis-tools/index.html
* https://www.msaccessgurus.com/tool/Analyzer.htm

How do people keep track of VBA functions/queries you have written in a database and have the ability to look them up?

That's a good question.
The first thing I would say is: a standardized concept for naming procedures, etc.

 

[Gasman]

Here is another.

Code Documenter

 

[The_Doc_Man]

The best way to start is to start from the beginning. Come up with a desired layout for the document.

I can only tell you how I approached this problem. I used Word and laid out chapter headers for introductory sections, historical sections, five content sections (I had no macros), relationships, and some detailed deep-dives into the effects of certain user interactions.

The introductory sections focused on: What was the purpose of developing the app? What was the business environment? What was the goal?

For the history sections, the IDEAL case probably is no longer possible since your project is now 20 years old, but where possible, you should include notes on various objects (appropriately segregated) to show why those objects are like they are, i.e. what conditions and decisions shaped your choices? How did they get where they are now?

I would put sections on external items - importing files if that is what happens, and a separate but related section on the References (external libraries) you use. And why you use them.

The incredibly tedious part is that number of tables and queries and other objects. For proper documentation, you should mention - at least in passing - why EVERY table exists, why every query exists, etc. This CAN be done any way you like. For some of my piddling little tables in my big projects, I used a (Word) table for the short, relatively static Access tables that were used to drive combo boxes or list boxes for the choices available to users.

For you, perhaps it is 20 years too late, but this might be instructive for others with potentially large projects. Start early. Document often.

 

[jdraw]

BigJoe,
You have been given great advice. I would also suggest this which is also from Crystal/strive4Peace. You can run this tool right now to get a detailed look at your current database and all of the components. It offers reports and search facilities to find "things". It can be run and rerun as/when needed and can be quite helpful.
You may also want to consider:
- a technical overview document for any techies who are involved in the eventual takeover,
- an operations document/chapter to describe what has to be done on say daily, weekly, monthly, yearly basis or whatever timeframe is relevant.
- clear instructions regarding backup and potential recovery (these rarely get full attention in set ups like you describe)
- your might consider some sort of video(s) however amateurish showing how certain things are done.

Note: There are several related tools on Crystal's website -- all free!.

 

[Pat Hartman]

@Gasman the code link in Crystal's tool is broken.

@BigJoe9090
There are four basic types of documentation you might want.
1. System overview. High level. Talks about "what not how" in terms even an manager can understand You can provide a link to the application menu here should the user want more detail. Start building a data dictionary as you write so that eventually every different field has a definition and validation rules defined.
2. Use Case. Identifies tasks performed by the system's users and provides details of how to find the task from the application menu and then goes step by step regarding data collection and calculations and finally output. After, you document from the user perspective, you might want to consider including references to specific forms/fields/tables. This is actually one of the most important aspects of documentation and it is ALWAYS done BEFORE you write the code and create the objects and tables to support it. This is what tells you what you, the developer, need to know in order to design the interface and schema and validation rules and all the other aspects of the code.
3. Schema. The overview is a diagram that shows each table, its PK, and its relationships with other tables. But it also contains additional detail that lists indexes and field defaults or validation rules including Triggers.
4. Application. The overview is usually the application menu. The drill down goes through forms and buttons down to code. Be very careful with this because there is the tendency to state the obvious but it is more important to convey the why of the code. Only explain code whose function is not obvious or which deviates from what might be considered "normal". Document changes as they happen. Usually, you will start by commenting out unused code but after testing the new version, all unused code should be removed. You can create a code repository for code you might want to use again rather than leaving it in place where you'll never be able to find it again and useless code just clutters the view and makes it hard for the reader to focus on actual details. At the form level, the most important documentation is the validation rules.

The easiest to create is #3 since much of that can be automated and is available using reports in Access.
#1 is important because it gives the reader context. You can start with a very skimpy description and flesh it out later. You
#2 is also fairly easy (after the fact, as in your case) because you can frequently get the users to do this for you. It is especially helpful if you can get multiple users to each write their own version of the use cases they use.
#4 is the most detailed and therefore the most difficult. You have to be very careful at this level because documentation that is out of date is downright dangerous. That is why you don't comment the "code". You explain what it does AND you always update the explanation when you change the code so this requires a lot of discipline. Less is more when it comes to code comments. Don't clutter the module with trivialities. Frequently, "why" is very important. For example, if you have an old application that you are converting. The old system didn't have good rules on validation so you might need to explain some validation choices. So, when you have a new record, you require both First and Last names to be added (always set the AllowZeroLengthString property to NO for text fields or else you'll be sorry). However, you don't want to punish the user for bad data that already exists, so for existing records, if this particular validation rule fails, you might want to give the user the ability to override and save the "bad" record. Just make sure that you haven't allowed the user to change something to nothing and you would use the OldValue property to help with this.

Some of your documentation should be built using Access. That allows you to build a data dictionary, for example but not repeat the validation rules in multiple places. The validation rules all belong in the dictionary rather than in the code or in the table description.

Here's an example of how documentation is incorporated into the application. Click on a report from the Reports listbox.

 

[Gasman]

The 140924 link works for me?

 

[Pat Hartman]

The 140924 link works for me?

That is not the tool the original link pointed to.

This is what I was looking for.

Code Documenter for VBA

Document code in VBA projects for Access, Word, Excel, and PowerPoint to an Access database

www.msaccessgurus.com

 

[strive4peace]

thanks Paul @Gasman, Jack @jdraw, and @Pat Hartman

I no longer have ability to upload files myself to accessmvp.com so more recent versions are on msaccessgurus.com

Code Documenter will be updated (sometime) with new features by Kent Gorrell.

Code Documenter for VBA

Document code in VBA projects for Access, Word, Excel, and PowerPoint to an Access database

www.msaccessgurus.com

for those who can come, Kent is presenting tomorrow in case you happen to be up ... he's brilliant.

The Power of Sub Classes in VBA​

The Power of Sub Classes in VBA with Kent Gorrell - Pacific - AccessUserGroups.org

Please join us for our Monthly Chapter Presentation January 4th, 2024, at 6:30PM Pacific (UTC -8) Kent will draw on his decades of experience providing solutions for his clients to share his insights into the Power of Sub Classes. Here’s an outline of what you can expect, in an Overview, a...

accessusergroups.org

date: 1/4/2024
time: 6:30 pm Pacific (wee hours for Europeans and those further away, on Friday!)

 

[Thales750]

Feed some code into the Chat GPT and ask it to document it for you

 

[KitaYama]

The first thing I would say is: a standardized concept for naming procedure

You may laugh at the idea, but we have a table that keeps a list of all functions and procedures, along with a field for their purpose, another field for a brief explanation about them.

A form is used to search for any keyword within the table to make it easier to find a specific function.

We have several FEs that have been around and being developed continuously for the last 25 years or so.
And as far as I remember, at least three developers have been working on them. When inheriting a database, specially a complicated one, it’s hard to find a function written by someone else. Even if there have been only one person working on a database, I believe it’s somehow impossible to remember a procedure that have been written more than a decade ago and re-use it in a new function.

 

[Josef P.]

I previously used doxygen and have since switched to Natural Docs to document the interfaces of classes or general (public) procedures.
It has to be possible for me to do this while writing the code, otherwise I'm not consistent enough to document it somewhere afterwards

Example: https://source.access-codelib.net/doc/ACLib-DbConnection-Framework/

I believe it’s somehow impossible to remember a procedure that have been written more than a decade ago and re-use it in a new function.

100% agreement.
And if you have naming rules, the compiler may complain that this function already exists after you start rewriting it because you don't know anything about the existing one.