Determine which tables are no longer used

[CedarTree]

Hello - inherited an Access database with lots of tables that I suspect are not being used. How can I "audit" which tables are not not being referred to in any queries, modules, etc. so that we can slim down the DB (I would move the un-used tables to a backup DB just in case of course). Thanks!

 

[GPGeorge]

The Access Database Documenter would be a good starting point.

One method I've used is to rename all suspect tables from say, tblYourBestChoices to Z_tblYourBestChoices. Run the database through all of its functions. If something fails because of a missing table (form's recordsource, queries, etc.), then you rename it back and move on.

 

[isladogs]

Another built in method is to view the object dependencies for each table
However, you can only do that with one table at a time

If you have a large number of tables or databases to check, you might find my database analyzer useful:

Database Analyzer Pro

The professional version of this utility allows you to quickly analyze the contents of any Access database. Features include view and analyze database objects, object metadata and get object sizes. You can view table data and table design. Also view table fields and field properties. View query...

www.isladogs.co.uk

You could also check out other third party tools including:

ACCESS Dependency Checker - Overview

 

[Pat Hartman]

One method I've used is to rename all suspect tables from say, tblYourBestChoices to Z_tblYourBestChoices. Run the database through all of its functions. If something fails because of a missing table (form's recordsource, queries, etc.), then you rename it back and move on.

This only works if you have Name Autocorrect turned off. With NAC on, Access will help you out and fix up the links to use the "new" table name.