MS Access

Microsoft Access – the missing report

Although the proper way to design a database is to start by using a design methodology and only then build the database many people and, in my experience, most students build the database first then back out the design documentation with various reporting tools, and go on to iterate around this loop ’till looks right. Certainly this works for databases of low to modest complexity.

Although most enterprise databases, and even MySQL lend themselves to this approach, Microsoft Access does not.

The only way I can find to back a data dictionary out of Microsoft Access is to go into design mode on a table and screenshot the details of each column in turn.  Bad idea…

The MS Access relationships diagram doesn’t indicate Primary and Foreign keys and so is a poor stand in for an ERD.

A Better way

The attached files offer a way of reporting data dictionary-type and ERD-type  information  from a Microsoft Access database.  There are three VBA source files. They are offered as source for several reasons: users can satisfy themselves there is no malware lurking within; they can be extended; and even in a locked down environment they can be added to an Access file – by copy and paste if need be. The code is by no means exemplary; in fact I doubt it is possible to write exemplary VBA code…

Examples

Entity relationship-type report

Example "ERD"-type diagram with Primary and Foreign keys

Example “ERD”-type diagram with Primary and Foreign keys

Data dictionary-type report

Link to a Data dictionary-type report

 

How to get and use the files

  1. Download the three files from here to somewhere where you will be able to find them, like your desktop
  2. Open the Microsoft Access file you want to report on
  3. Back it up, then open a copy
  4. Insert the files in to VBA:
    1. Choose Database Tools | Visual Basic | Files | Import and import each of the three files (one at a time I am afraid)
  5. Hit the “play” icon on the toolbar (green triangle)
  6. If you want to re-run the report first stop the code with the square stop icon then go to 5 above

You will now have two new files in the same folder as your Microsoft Access file. Check the time and date to make sure they are the ones you have just made.

  • Schema.html — click on this file to open a report showing each column in each table, with PK/FK, datatype, and description. This can be copied and pasted in to Microsoft Word for further processing
  • Schema.dot_  — this file can be rendered as a relationship diagram.
    1. Go to http://www.quanglewangle.com/dot/upload.html
    2. Upload the schema.dot_ file
    3. The resulting image may be saved locally to be incorporated in your report

Report any issues as comments to this post

 

 

Discussion

No comments yet.

Post a comment

Leave a Reply

Your email address will not be published. Required fields are marked *