How Do I List All Tables used in All Queries?

R

rj_budz

I need to know what data my database app is using from two linked tables that
are soon to be replaced by an "improved" program. The designer of my
(adopted) DB just linked the tables in their entirety, but I need to know
specifically what fields are being used where in the application (front-end).

So, how do I get the tables & fields referenced in all my queries? There
are over 100 of them, so opening each isn't my first choice.

I can write VBA code if need be.

Thanks!
 
L

Larry Daugherty

There's a shareware product named Find And Replace available from
www.rickworld.com.

There is also a commercial product named Speed Ferret.

Both are highly endorsed by people posing in these groups.

HTH
 
R

rj_budz

Thank you for the responses!

I'm going to go through the database with the currentDb.QueryDefs.Count
etc. method, primarily because my company is cheap. I can also go through
the forms & reports this way as well (some of the ~sq_ queries seem to hold
that).

I will post the code I write for completeness, in case someone else stumbles
across this thread in hopes of an answer.

Thanks again!
 
R

rj_budz

Follow up:

Since I already knew the ODBC string to connect to the database in question,
it was just a matter of looping through all the queries and writing the ones
with that string:

For x = 0 To currentDb.QueryDefs.Count

SQLString = currentDb.QueryDefs(x).Sql

If InStr(SQLString, "DatabaseName") Then
Debug.Print x & " : " & SQLString
End If

Next

In my case I needed to write the results to a text file because I had a
rather large number of results, but this is all there really is to it.

I didn’t find a property to return the table name per se, like the fields
property (CurrentDb.QueryDefs(x).Fields(y).name), but the SQL strings can be
broken down.
 
Top