Analyzing access databases
Where I work they have over a hundred Microsoft Access databases that act as interfaces into their corporate SQL data. These databases range from Access 97 to Access 2003. My client would like to get away from this code base but so much of their business rules are embedded in the access code (VB6) that it has proven difficult to even contemplate ditching MS Access and VB6. Lax version control has made the planning much more difficult and tedious. Live and learn, right.
To get a handle on the task at hand I wanted to document all the databases by recording all the objects into another database. A meta-database, as it were. Didn’t seem like a big deal until I started trying to access different properties of some of the different objects I was interested in; specifically the record source for reports and forms and the content of the code modules. I came to the conclusion in the process that the Microsoft object model is so unwieldy that I now categorize it as crap.
So the Internet search for knowledge began and the insight that I needed to finish my little utility came from John Barnett who wrote a similar utility called mdbDoc that builds a nice html document of all the db objects contained in an Access application. His app was set up as an MS Access add-on (.mda) that could be executed from within any Access application. It is a very nice piece of work except that the html formatting is embedded with the code and that it has to be run one database at a time. I needed to document a couple hundred databases all at once and with an eye toward consolidation I needed to be able to view all the queries from all the applications sorted together in one place. Same with the code modules and tables. Though his utility wasn’t suitable for what I needed I was able to configure it as if it were a function in my app and attached the resulting HTML doc to a rich text field in the database structure I built for each .mdb file.
There were a couple of subroutines in John’s code that I was going to have to write or do without so with much gratitude I borrowed the ListCodeBlocks and dependent subs from John Barnett’s mdbDoc. I have a nice Logger class that I use when scripting in Domino but since I was doing this all in VB6 and I am so rusty with VB, I borrowed John’s mdbdclsFileHandle too. Another nice piece of work that takes away all the mundane tasks of reading and writing to disk.
So here it is. The following code builds a Domino database with a record for each MS Access object. The back end could just as easily by SQL Server or MySQL. I just chose Domino because it was easily accessible and convenient. Now I can sort by object type regardless of what database actually contains the object.
To use the app you fill an array with all the target directories you want to scan. You can rig it to crawl servers but in my case that was extra features that weren’t needed.
When you start the application you check off the objects you want to document and click ‘Do It!’ When the app finishes you open up your target database and look at the results. In Domino it looks something like this:
The main code follows. If you want the modDocumenter code or the file handle class in John Barnett’s mdbd utility, you can get it from his site, linked above. (If you have a free editor like like PSPad or ConText, paste the code over there. To me it’s easier to read code that way.)