by Mark Trescowthick - GUI Computing
All the tools I've seen for documenting Access seem to have a very techoid-centric view of just what "documenting" a database means. As a database designer, my need is to communicate to end users what’s going to be included in their new system - and none of the tools seem to do that very well. Therefore I set out to do it myself...
The breakthrough was realising that Access’ own Database Documentor could save its normal report output as a table (after all, it had everything I wanted). So I thought I’d have peek at what this table looked like.
It turned out that it was a little on the unhelpful side (columns called ‘Extra1’, ‘Extra2’ and ‘Extra3’ are hardly intuitive) but it was nevertheless usable (eg. ‘Extra1’ was better named ‘Description’, ‘Extra2’ looked more sensible as ‘dataType’ and ‘Extra3’ was really ‘Length’).
With this in hand, the first step was to create a standalone database (which I called ‘DOCUMENT.MDB’) which, in the first case, ATTACHed to the standard ‘Object Definition’ table created by the database documentor. This left me with a table :
|ParentID||Long Int||Extra2||Text (255)|
|Object Type||Memo||Extra3||Text (255)|
I then created a simple Query called docAlias which was designed to rename these columns more sensibly.
SELECT DISTINCTROW [Object Definition].ID, [Object Definition].ParentID, [Object Definition].[Object Type], [Object Definition].Name, [Object Definition].Extra1 AS Description, [Object Definition].Extra2 AS DataType, [Object Definition].Extra3 AS Length FROM [Object Definition];
Having got this far, and mightily encouraged, I worked on extracting the Tables from a mass of data:
SELECT DISTINCTROW docAlias.[Object Type], docAlias.* FROM docAlias WHERE ((docAlias.[Object Type]="Table"));
I called this query docTables (surprise, surprise). Now what I needed was the Column information, so the next query off the rank was the docCol query :
SELECT DISTINCTROW docAlias.[Object Type], docAlias.* FROM docAlias WHERE ((docAlias.[Object Type]="Column"));
These two queries provided the bulk of the data, but two key (so far as I was concerned) areas remained : what were the Descriptions, and what columns were mandatory? I had a quick look at what was involved in determining mandatory and optional and immediately decided to get the descriptions out of the way first!
Both Table and Column descriptions were very straightforward. The docDesc query handled them :
SELECT DISTINCTROW docAlias.Name, docAlias.* FROM docAlias WHERE ((docAlias.Name="Description:"));
Simply joining this query with the docTables and docColumn queries was going to give me what I wanted. In fact, that’s as far as I need to go with Tables, so I created the final Table-based query, docTableData :
SELECT DISTINCTROW docTables.Name, docDescription.Description, docTables.ID FROM docDescription RIGHT JOIN docTables ON docDescription.ParentID = docTables.ID;
So now, all I had left was the mandatory/optional character of columns. The first cut (in a query I dubbed docRequired, more in hope than anger) was pretty straightforward :
SELECT DISTINCTROW docAlias.Name, docAlias.* FROM docAlias WHERE ((docAlias.Name="Required:"));
but this didn’t entirely do what I’d expected, for a couple of reasons.
First, it returned an assortment of results. It seems that Access stores the ‘Required?’ data as one of ‘Yes’, ‘No’ or ‘1’... now there’s consistency for you! Still, I could work around that. What was even more puzzling, was that COUNTER columns didn’t have any mandatory / optional data at all.
I finally constructed a query, docCounters, which extracted these :
SELECT DISTINCTROW docAlias.Name, docAlias.Description, docAlias.ParentID FROM docAlias WHERE ((docAlias.Name="Attributes:") AND (docAlias.Description="Fixed Size, AutoIncrement"));
This left me with one final whopper query to drag all this diverse column data together. I called the query (no prizes for guessing) docColData :
SELECT DISTINCTROW docCol.ID, docCol.ParentID, docCol.Name, docDesc.Description, docCol.DataType, docCol.Length, IIf(CStr([docRequired] ![Description])="No" And [docCounters]! [parentID] Is Null, "No", "Yes") AS Reqd FROM (docRequired RIGHT JOIN (docDesc RIGHT JOIN docCol ON docDesc.ParentID = docCol.ID) ON docRequired.ParentID = docCol.ID) LEFT JOIN docCounters ON docCol.ID = docCounters.ParentID;
Having dragged all this together, it was a relatively simple matter to construct one final, summary, query on which to base a report :
SELECT DISTINCTROW docTableData.Name, docTableData.Description, docColData.Name, docColData.Description, docColData.DataType, docColData.Length, docTableData.ID, docColData.ID, docColData.Reqd FROM docTableData INNER JOIN docColData ON docTableData.ID = docColData.ParentID ORDER BY docTableData.ID, docColData.ID;
Naturally, I named this query docSummary. Now, I could produce my report.