<

Database Ghostbuster -- How to Safely Free Your Database from Ghosts

Published on
9,132 Points
3,132 Views
Last Modified:
Approved
Annaliese Dell
Find Annaliese on Amazon, Barnes & Noble and VBQuick.com.

What is a Database Ghost?


A ghost table or query is an object disembodied from its host. The host can be a form, report or code. These ghosts do not rattle and knock about the database wreaking havoc like mischievous poltergeists. Their haunt is more subtle.

Ghosts are created by deleting or rewriting a form, report or query that leaves the underlying table or query host-less. Thereafter, its disembodied spirit haunts by sapping energy in the form of kilobytes and hiding in the list of living objects one must sort through to find a needed one.


Do You Need to be a Database Ghostbuster?


The solution is as simple as determining if you need a solution.

Ask yourself how often you:

    1.      Delete a form and create a new one because you found a better way to accomplish the task?
    2.      Forget to delete all the tables and queries associated with a deleted form?
    3.      Search the objects list trying to determine which tables and queries you can delete?

If you answered yes to any of the above, especially number three, the following solution provides a significant organizational return in exchange for a tiny investment.

If you have read and use The Link Principle (Amazon, Barnes & Noble), {Ed note: By the author of this article} you appreciate the use of abbreviations to organize files and speed searches using any search method on any operating system.

You can use this same method to safely delete ghost tables and queries.


What are Re-creatable Tables and Queries?


The following examples use forms but reports and code may be used interchangeably. The words tables and queries are equally interchangeable.

Tables can be created by:

    1.      manual entry
    2.      different types of queries
    3.      code

Some examples of tables the database can re-create are:

    1.      Imported or linked data. These types of tables generally need updating to maintain sync with the original file. Some examples would be:
        a.      checks imported from a bookkeeping application
        b.      vendors, employees or customers imported from a bookkeeping application
        c.      contacts imported from an email application
   
    2.      Queries combining table data. These queries may be created by procedures, especially when the queries use different tables containing the same data. Some examples would be:
        a.      Imported checks stored in tables by date joined to a table of vendors. The query procedure would use a variable for the check table(s) rather than a permanent query using one specific table.
        b.      Imported invoices stored in tables by date joined to a customer table. The query procedure may contain variables for the invoice table as well as the customer.


The rule is: if the database can re-create the table AND its data:
   
    1.      the table can be safely deleted
    2.      the table name should denote this


How to Identify Ghosts


Use an abbreviation that makes sense for you to denote tables the database can re-create. The abbreviation should be a suffix preceded by an underscore. Two letters are sufficient but three or four may be used. Some examples:

    _RL: reloadable
    _DC: database created
    _tmp: temporary
    _DL: deleteable
    _QC: query created
    _CC: code created
 
Note that DC denotes the table was created in the database while QC and CC provide additional information of how the table was created; more bang for the character count.

Adding suffixes to tables whose structure AND data are created through code or query lets you safely spot disembodied ones. You could even conceivably delete all tables and queries with specific suffixes without consequence so long as the database can re-create the table AND the data.

You may already be thinking ahead to a procedure that automatically deletes re-creatable tables periodically or when called.


Avoiding Recordsource Errors


To implement this method, you must incorporate a way to avoid errors like the one below:

    The recordsource recordsource specified on this form or report does not exist.

This error appears when opening a form whose recordsource no longer exists. If you delete all the re-creatable tables, this message will appear each time you open a form before it’s underlying table is recreated. Therefore, testing to see if the recordsource exists is best accomplished outside the form.

Similar messages appear for controls whose recordsources are missing.

Resolve the missing recordsource issue with:

    1.      a procedure that iterates through a list of required tables that;
    2.      recreates missing tables and;
    3.      automate the procedure to run at database startup.

Keep the table list in:

    1.      An array in the procedure itself or;
    2.      A permanent table in the current or backend database

Make sure to add new re-creatable table names to the list wherever you keep it.

Place the table creation code in public procedures in modules.


Summary


Make deleting ghost tables safer by using abbreviations to denote tables the database can re-create.

Include a startup procedure that iterates through required tables and recreates them if missing.

Only use this method if the database re-creates the structure AND data, as in imported tables.

Become a Database Ghostbuster and rid yourself of those disembodied objects!
0
Comment
0 Comments

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month