Documenting a Access Application for Developers [closed]

Question

I need to document a MS-Access application that was created, developed and maintained completely by a power-user over 10 years.

This is an interesting situation because what they want is a manual so that a future developer can come in without prior domain knowledge and make changes to the frontend or the backend in a timely manner.

There are a few questions on my mind for this little project:

• What is a good manual design creating application? Microsoft Word doesn't quite cut it.
• What kind of things would you, the developer, need to know in order to make changes to things like forms, reports, tables or other Access objects?
• Anything else I missed? Any pitfalls?

Chuckhole

I'd say as a developer I'd prefer good code commenting, uniform naming conventions and good design over a document I'll likely never read. When you say "without prior domain knowledge" it's not clear if you mean of Access or of the specific application. If the former, good luck with that -- you could write a book. If the latter, see my first comment! – Odeen

I mean, the way the business operates as the 'domain knowledge' source. There are a lot of quirks, unexpected business rules and other behaviors that I've spent the couple weeks learning about that are extremely relevant to how the database is set up and how the application works as a whole. – Chuckhole

Trouble is these kinds of documents are seldom updated once they're done. Maybe the idea of a Wiki which the users create would be an idea. And the idea is that any new users always post their question(s) in the wiki and then get the answers and update them. And they have to be given the time to do so. Also why not do this within the Access app. Create a table with one field by form name and another memo field. Each form gets a button which opens a form against that table creating/update the record corresponding to that form. Then the users update the memo field right there. – Schick

It's a low maintenance program for the most part. There are already mechanisms in place for any kind of a problem (and the document is supposed to supplement this). A wiki or 'bugs' table is just not applicable, but it is a good idea. Also, there are only 3 main users of the program plus some ancillary users. – Chuckhole

A document of application notes that captures the domain knowledge specific to the app and that the last developer picked up in the process of working sounds like a good idea. Beyond just a bunch of notes, loosely organized, I can't see much else. I do second Tony's Wiki recommendation. It's an awfully good way to capture and maintain this kind of information (though not necessarily justified for every project). – Odeen

Answer 1

In my experience, Access and VB6- based programs are plagued by more code replication and technical debt than programs in mainstream languages. I'm not sure why. Maybe it's the nature of Access as a "prototype" or "toy" database (though it can be quite powerful when yielded correctly).

If I had to choose between expending time on documentation and expending time on reducing technical debt, for example by remodularizing, eliminating repeated code, splitting long functions, etc., I would choose the latter. The improvement to maintainability and readability would be greater.

Arcane

I would say it's the result of Access being so easy to use that people who don't know anything about best programming practices are able to create applications in it. It's not Access's fault any more than it's HTML's fault that there's so much "tag soup" out there on the web -- it's a FEATURE not a BUG. – Odeen

I would second your actual recommendation, though -- refactoring for maintainability is going to be a much better use of a developer's time than thoroughly explaining how the strands of spaghetti relate to each other! – Odeen

@David: When I was in high school and freshman year of college, I made supplemental income of fixing databases for people who wrote applications without knowing anything about database normalization. – Arcane

@David, unfortunately, almost all of the application is in Access objects and not VBA other than what is generated by default for opening up the reports/forms. – Chuckhole

@Uri: I'm 30 years out of high school this year, and I still make most of my living from fixing Access databases created by people who don't know what they are doing. It's not a flaw in Access -- it's actually one of its greatest strengths, because it enables those without a high level of expertise to create something that is useful. – Odeen

@Nitrodist: Access objects document themselves quite well. For example, a main form/subform indicates a parent/child relationship between the tables involved. – Odeen

Answer 2

You could start with generating some automatic code documentation using MZ-Tools add-in for VBA. The same add-in can help you clean unused variable declarations, generate line numbers, reorder procedures within a module, etc.

Documenting forms is more difficult. My proposal would be to keep a screen shot, alltogether with a .txt file obtained through the undocumented application.saveAstext method.

Tashia

Thanks for the tip on MZ-Tools. Are there any other MS-Access/VBA tools out there similar to that? Also, the amount of VBA code in this application is near zero. It's all pretty much in Access objects. – Chuckhole

Answer 3

I know this is closed for long, but I can't refrain adding my 2 cents:
In the case mentionned, I think the most usefull doc to produce is a FUNCTIONAL documentation (which should have existed before starting the development in an ideal world).
Second is within the code itself, and that includes the VBA but also the field descriptions which can be set in Access and SQL Server.
Third is a (or a set of) nice database diagram. Once you have that, all the rest can be generated by the new developer using HIS favorite tools.
Speaking about tools, I particularly like and recommend:

• MZ Tools: specially to easily find which routines call the one your looking at
• Smart Indent: to properly indent code. Trying to read badly indented code makes me sick
• SqlSpec: (not free) generates HTML doc of the database itself for most database engines

Unweighed

-1 ? please comment why so we can all learn ! – Unweighed

I hate when people -1, for no good reason. I thought your comments were useful. Maybe not the final answer, but not a reason to vote down. – Eulau

Answer 4

Have you tried the using the built in database documenter? It will print out all tables, indexes, forms, controls, each property of controls. Code, the sql used and just about any thing else. This results in huge, but just massive printouts. However, while it will kill a few trees in the process it sure is a great way to impress the boss.

Numismatology

Yeah, I've taken a look at it and, like you said, the results are huge. Since the tables aren't formally linked with FK's, it misses a lot of of those relationships. It's not exactly the greatest tool when it's generating over 200 pages XD. – Chuckhole

Future developers who have to work on the project would thank you if you took the time not just to document the app, but to implement proper referential integrity. And, yes, it would likely involve significant data massage to fix up or delete orphaned records. I've done this a bazillion times when hired to take over apps created by less-than-stellar developers. It's usually the first thing I'd do before any real work to enhance the application itself. Working on a front end with a crappy data schema behind it is like trying to polish a turd. – Odeen

Yeah, this little project has 2 phases. Documenting the current project as it is right now and then myself creating a second version that's properly made which will most likely be an adventure. – Chuckhole

Is it worth documenting the stuff you're going to replace with something better? – Odeen

Yeah, it's going to be months. @Odeen if you can summarize your thoughts you've made made in the comments as an answer, I'll accept it. – Chuckhole