What is the best way to document an Access Database? 6

[postermmxvicom]

I am working for a company during the summers making a database. I have no idea who will need to understand what is going on, so I want to make sure it is well documented so if it lands in capable hands it will live.

Now, I have programmed in C++, and making documents in code there makes sense. But, with all these visual interfaces for making structure and bits of code here and there...I just don't know how or where to comment.

So, my question is: How do I comment the database to keep it maintainable for me and others?

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[timfoster]

There are no hard and fast rules.

All the books/courses will tell you that you should comment everything and that you can never have too many comments. I know, because when I train people I tell them exactly that. The reality is different though. Most people new to programming in a business environment (sounds like you are a student if you're writing this in the Summer) start off with lots of comments everywhere. As you write more commercial code though these good habits fall by the wayside. There really is no substitute for good documentation, but finding apps that have it is a rarity.

Ideal world:
Comment all your code, describe all the tables/views/gui objects and create a data dictionary.

Real world:
Comment your code where it's a bit complicated or where you think you wouldn't understand it yourself in 6 months time and create a data dictionary.

 

[jrbarnett]

Comments in VBA start with an apostrophe, and are shown in green in the editor - for example:

' This is a comment

or begin with the word "Rem"

eg

Rem This is a Comment

In either case, the comment carries on to the end of the line. Both are acceptable in code, but in my experience use of apostrophe tends to be more usual these days.

There are free tools available for producing some documentation from an Access database (eg

http://mdbdoc.sourceforge.net

- disclaimer: I wrote this).

Other tips: use the Description tag on the Property sheet of forms/reports/modules to put something in. This is where MDB Doc (see above) gets a lot of its basic information from.

John

 

[postermmxvicom]

Thanks guys

Thanks for the data dictionary tip (I'll have to research that - I am very green)

Also thanks for the technicals on commenting VBA.



Is it inappropriate to have all the documentation, including comments on the VBA, contained outside of the database entirely? OR should I strive to have comments and documentation with the database and in the code using the apostrophes?






One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[geolemon]

The risk with documenting outside of your database instance is this:
What happens if your documentation file is lost, or perhaps simply someone using the database doesn't know it exists, or inadvertantly moves it, etc?

Not to mention, if you use comments (just like you do with C++) in VBA scripts, they are context-appropriate and can be used very finitely. You can be as descriptive as you like in any given commment without creating the impression of a Whoa! This Is Too Much To Read! multiple-paragraphs of text, like you will if you create one external document.

The Access database itself is very easy to document - MS was nice in how it provides space to document metadata right in each table - each column I'm sure you've noticed has space for a nice, descriptive comment (don't skimp), and you can describe your tables as well. The relationships are pretty self descriptive - and if set up properly, of course no one will need to touch them anyway. In fact, I'd document places where you don't have relationships enforced to explain why you made that decision, what the risk would be if someone *did* check the box to turn it on.

So I think the short answer is - I believe most professionals would say that "yes, it is iinappropriate to document entirely outside the database environment"
...but that's not to say external documentation for other (non-DBA) purposes wouldn't be valuable, and external documentation stripped of the documentation that's generally expected to be inside the database and VBA code would even inherently seem to be concise for whatever purpose *that* documentation would have (perhaps higher level data flow diagraming or end-user training documentation?).

 

[jrbarnett]

Software documentation generally falls into two categories:

Programmer documentation
Used by programmers, DBAs, technical writers etc, for help with understanding code, debugging, adding new features.

This is things like MDB Doc output, comments in source code, an entity relationship diagram (from Tools -> Relationships window) etc.
If you have any security systems in place, you also want to put in details of how to do forgotten password resets (and records of any master login IDs and passwords etc). If it connects to a back end database, connection information, versions used (including ODBC drivers) etc.

User Documentation
To be read by non technical people who use this application to explain:
i) How to use the application, in simple non technical terms.
ii) Basic troubleshooting information where known issues are covered.

This should includes lots of screenshots, examples, possibly a windows help file, it should not assume technical knowledge of how the system works, and any instructions given should relate to existing business processes so that the people can see how it works for them.

The rule about what to put in is simple: "Would I want to know this if I were reading this document?"
If the answer is yes, then put it in. If no, then leave it out.

John

 

[MasterRacker]

postermmxvicom,

Part of the output of John's mdbdoc tool <i>is</i> a data dictionary. The only thing I didn't see, (in my admittedly brief glance) was foreign key relationships.

John, That tool is going to save me a lot of typing in the future. Thanks.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[postermmxvicom]

Thanks guys, I think those posts had lots of things I really needed to hear. jrbarnett thanks for the tool (I am going to go check it out next) as well as some of the fundamentals; and thanks MasterRacker for elaborating on that tool some. geolemon and timfoster thanks for your rubber-meets-the-road perspective.

I believe y'all have added much to this project and I am sure those who inherit this project from me also thank you

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[MajP]

One of the biggest things is to make sure you use a good naming conventions in all of your database objects. I think most people can pick up my databases and figure out what is going on without a lot of documentation because my naming conventions provide a lot of information. There are a lot of standard Sql, access, and vb standards. You can use your own as long as it provides information. EX:

tblPeople (a table containing people info)
autoPersonID (a Person ID primary key field auto number)
strLastName (Last name field which is a string)
strSSN (SSN stored as a string not a number)
memoBackground (a memo field)
dtmBirthDate (a date field for birth date)

qryPeopleByLastName ( a select query sorted by last name)
xQryPeople_Age (a cross tab query with people and age info)
txtBxPersonID ( a text box with a Person ID)

There is nothing worse than someone asking you to look at code and the form is called "People" and it is based on a query called "People" which comes from the table "People" and has a field "Person". and a subform called "People List".

Also make sure no spaces ever in any name. And no reserved words. Nothing worse than a date field called "Date".

 

[postermmxvicom]

MajP,

Unfortunately, I was already knee deep before I came across this wisdom. :/ While my naming convention is descriptive, it is not as good as what you mention and does have some of the trappings of what you described as "bad"


Sooo....do I just rock and roll with it? or do I go back and rename everything? And if I should rename it all....wow! How do I do that? and make sure it is all right?

This is my second summer working on this project so...it is more than just started....

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[jsteph]

posermmxvicom,
One little tidbit I do for forms and reports is to use large lables set to.visible=false, with red text.

I put documentation in these labels so a developer can see it in design view--sometimes it's direct comments and documentation about the form/report, but often it's just a "HEY!! Look in the code-behind for commented documentation before you start messing with this!"

--Jim

 

[MajP]

Sooo....do I just rock and roll with it? or do I go back and rename everything? And if I should rename it all....wow! How do I do that? and make sure it is all right?

That will have to be your call. The work to benefit may be too large and you will spend your summer trying to change your names. However, there is a lot of code that you can find on the web to help modify your naming. VBA extensibility can even modify your code.

I make a lot of "code heavy" databases so for me it is usually worth it to clean up the naming. It allows me to write code without having to open up a form, table, or query and I know what I named a certain object. You can spend a lot of time opening things back in design view to see what you called them.

For me if it is a field for a person's first name it is always strFirstName
not
FName
F Name
First
Name
First Name
If that field is on a form then it will be one of these depending on the type of control
cboFirstName
txtBxFirstName
lstBxFirstName
optnFirstName

So without opening the form I know that if I used a combo box then I can reference that control
cboFirstName

 

[jrbarnett]

MasterRacker - Foreign key relationships are there - look at the Relationships category.
For example:

http://mdbdoc.sourceforge.net/northwind14.htm#mdbRelationships

John

 

[MasterRacker]

postermmxvicom,
What MajP is advocating is is VB6 style naming. Even though the VBA in Access is essentially VB6, you may want to consider using .NET style naming for your source code (Strangely the published .NET standard doesn't cover UI elements.) Also keep in mind that code naming is different than database naming. Database naming has spawned some long and interesting discussions (arguments) over the years.

Pete Brown has published an excellent set of articles on naming here:

http://www.irritatedvowel.com/Programming/Standards.aspx

John - thanks.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MajP]

Actually, I really do not advocate any standard (For myself, I basically combine a bunch of them together) as long as it is descriptive and provides the db/code maintainer information. Some people get really parochial on the naming convention and it gets overly complicated. However, most people who post to this site have a different convention than I use, but I still can quickly read their code and know what is going on.