Cleaning Up An Access Database

Author: Garry Robinson from GR-FX, June 2002

One of the hardest things to do with an Access database is cleaning up all the objects that are no longer required in a database.  This article outlines how the crew at GR-FX go about this task. 

Working out what is actually being used

If you ask 5 different users of a database what is important, you probably have at least 5 different answers.  We handle this by using the logging systems that you will find in The Toolbox.  As each form or report is opened, an entry is made to a log table that shows who opened the object and when.  After a month or two, you can review these details using reports or the log itself and if a report hasn't been used at all, then it is a candidate for archival.   Opening the form or report requires using a custom open command in visual basic that works the same way as the docmd.openform or openreport methods.  Click here to read more.

Tools To Work Out What To Delete

There are 2 tools that we use to work out what to delete apart from plenty of backups.

Total Access Analyzer from FMS Go to web site  Called FMS TAA below
Find and Replace by Rick Fischer Go to web site

Working Out What To Delete

There are 4 reports that you need to run from Total Access Analyzer with all objects in the database

All objects in the database (Sorted by Object Type)
The application diagram.
The object diagram
The data diagram

Save these reports to disk as snapshots or PDF files

Now you work through the application diagram and the object diagram and mark off on your "all objects report" all objects that it is obvious that you are going to keep, all objects that you need to test and all objects that it is obvious that you are going to delete.  When you finish the marking off these objects, you will find that there are probably a number of objects that you have not marked off.  These also become candidates for possible removal. 

Backups and Archives

It is important at this stage to back up your database.  Also make a blank database in an archive directory.  You will transfer objects that you are going to delete into this database just in case.  Placing the database in zip files is a really good way to track version changes.

Turning Off Name Auto Correct

In Access 2000 and 2002, there is an option that will automatically rename references in some objects to any object that you rename your self.  Please switch this option off now by going to the Tools Menu ... Options and selecting the General tab.  Now unselect the Name Auto correct options.

Conversion Of Macros

You probably should convert macros to visual basic if you can.  This will make tracking of what you are and are not using easier.  This can be a bit of slog though if your database is loaded with macros.

Remove Modules

Its probably a good idea to start with the modules first because when you delete a module that is used by another module as it will not compile.  Remember to transfer the module to the archive database as it must be deleted rather than renamed.

Reports

Reports are good things to remove as they will not necessarily stop your database from working and users will definitely complain if their favorite report is no longer available.  The technique to use is to first rename the report to "ZZZ Report Name" and leave it in the database for a month or two.  Then archive it.  The FMS TAA "object diagram" will assist in assessment of your reports.  Once you have remove a report, check that the queries that it uses are no longer required. 

Forms

Rename forms to ZZZ before archiving if you do have confidence in your ability to pick a form.  The FMS TAA "application diagram" will assist in assessment of your forms.

Queries

All old databases will have a number of queries that are not necessary to the full function of the database.  Use the same technique to rename the query to ZZZ before removing it a month or so later.  The FMS TAA "object diagram" will assist in assessment of your queries.  Once you have remove a query, check that the queries that it uses are no longer required.   Using the Cross Referencing in the FMS explorer can also help analyse which objects do not have related partners.  Start using this after you have cleaned out a few objects.

Tables

At the end of the cleanup, it is a very good idea to see if your tables are being used for anything.  Use the FMS DAA Explorer to find tables that have no cross references from anything else in the database.  The FMS "TAA Data Diagram" is a very good way to work out whether a table is being used for anything important.  Sometimes the table may only be used by one unnecessary query.  Before deleting a table, check the relationship diagram in your database.  If you are using a backend database, then do not forget to clean up the links and the backend table as well.  Using ZZZ prefixes is a safe way of archiving and linked or backend table before fully deleting it.

Fixing Up Errors

One of Total Access Analyzers best attributes is its error logging.  Take time to check these out when you clean up your database.  This error report is very good to provide a new client with when you take over a database from another developer.  With this, the client will know that errors are not all of your making and actually were inherited from a previous developer.

Searching

Removing objects will also require lots of searching of the database as well.  FMS TAA 2002 offers a great searching routine that is useful for multiple fast searches  of the database for object names.  The database explorer can also help in this regard as well.  Unfortunately, once you modify the database, you will need to refresh the TAA documentation database.   At this stage, you would be better to switch to Rick's Find and Replace tool. 

Do Not Be Shy - Clean Up Your Database

A database with extra unused objects will cost you a lot in developer money.  This is caused because you have to keep working through the database to make sure that changes to tables, queries and forms do not effect existing objects.    If you have additional objects, you have to change and test these as well to incorporate the changes.  Also additional objects make the database slower to load across networks and bigger to install etc.  All additional costs for someone.  So take a plunge every now and again and start cleaning up your mess.

Click on the button for the next help page in this Access Loop.

Other Pages at vb123.com that you might want to read
Implementing a Successful Multi-user Access/JET Application

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals