How can I get a list of the queries that use the data in a table?

A

AMB

I am new to Access and need some help please? I have a database that has
about 75 tables and I would like to see what queries (over 200) use the data
in the tables to see if I can get rid of some. Is there a way to get a list
of the queries linked to at specific table?

Thanks
 
A

Albert D. Kallal

AMB said:
I am new to Access and need some help please? I have a database that has
about 75 tables and I would like to see what queries (over 200) use the
data
in the tables to see if I can get rid of some. Is there a way to get a
list
of the queries linked to at specific table?

Thanks
Click to expand...

You very much have to go through one by one. Remember, it not only a query
uses a table, but what about forms that are based on that query?

And, what about reports that are based on that query?

And, what about code in modules that uses that query? (if you delete the
query, then you break the code).

In code you might go:

dim strMyQuery as string\



strMyQuery = "qryDeleteOldCustomers"

currentdb.Execute strMyQuery

Since your using a programming language to specify the actual query and sql
used in a code, then the only way to really be sure if a query is in use
would be to have a tool that actually understands and can READ actual
code!!!! not a easy task at all....

In a2003 you however right click on a query, and select "object"
dependencies. It will at least give a list of tables, forms and reports that
are set to that query. This information is very helpful, but would not still
allow you to delete the query if you have any code in the application.

note that this object dependences feature will turn on track-autoname
correct, and you should turn it off after you run this...
 
J

John W. Vinson

I am new to Access and need some help please? I have a database that has
about 75 tables and I would like to see what queries (over 200) use the data
in the tables to see if I can get rid of some. Is there a way to get a list
of the queries linked to at specific table?

Thanks
Click to expand...

There are some third-party tools that can search a database for all instances
of a tablename (in queries, in VBA code, or in forms or reports); Total Access
Analyzer can go further and give you a complete object crossreference (it's
correspondingly a more expensive product). Try:

Free: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson [MVP]
 
Top