Data Structure Analyser

by Pat Herley - Datacom Information Services

I found Tony Harris' article in the AVDF 4-4, August editon (Access Collections), interesting because of frustration with Access on two fronts. As Tony's article pointed out, loading up Access and wading through a myriad of detail, all for one structure definition was painful.

At the time I was documenting a System and needed to include Appendices of table information which would conform to type styles and the like. Therefore, hard copy printouts weren't entirely suitable.

This led to the other even more galling problem. In spite of promises to the contrary, I couldn't get a structure definition out to an ASCII file. Under MS Office, there supposedly exists the ability to get a structure definition into Word for Windows. But try as I might, the whole plot would go out to lunch after merely writing a bit of header information into Word. Having satisfied my primary requirement, I could see other uses, and what is now the final result grew from that point.

The front-end is standard issue with disk and directory list boxes. The file box is restricted to files with the "MDB" extension. One day this may grow to incorporate other types of databases/tables. Once a Database has been selected, all tables in the database, with exception of the System Tables, are displayed in a list box.

The list box is a multiselect type 1. As a selection is made, a second form containing a standard VB grid pops up displaying the structure for that particular table. In the event of multi-selects, the grid simply refreshes with the structure of the last table selected.

The grid rows are simply determined by a count of the fields in the table and the columns are set to three - Name, Type and Size. The total number of fields are displayed in a panel in the main form, which displays the number of tables in the database until a table is selected. The SetWindowPos API call is made to keep the grid form always on top, but it can be closed with an on-form button. This has proved useful when only a quick reference was required rather than hard or soft copy.

Two radio buttons allow selection of "To File" or "To Printer", and in the event of file, if the file output drive:/path/filename text box is not filled in, a dialog box informs the user and progress is stopped until it has been completed.

The main loop tests for which tables in the list box have been selected, and depending on the radio button choice, branch to either a print to printer or print to file routine.

This could've been made more generic with one (perhaps) routine handling both, but ....modularity, modularity modularity.

In order to determine the field type, I created a "Select Case" block in a separate function which is called from all routines - it was sure better than having 12 case statements appearing three times.

The output consists of all table information - Field Name, Size and Type, as well as all indexes, their keys and whether they are primary keys or not. I also included the other attributes of Date Created and Date Last Modified, as well as the total number of bytes used in defining the structure.

Error trapping is pretty thin on the ground, being restricted to only trapping error 52 "Bad File Name or Number", which generally suggests you are using the same output file name as one that already exists when writing the structure out to file.

Other bits of "prettiness" include trapping mousemove events for most controls to give some feedback via messages at the bottom of the screen.

Written by: Pat Herley
Oct 95