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!.

 

[Gasman]

The 140924 link works for me?

 

[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.