How Do I List All Tables used in All Queries?

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!

You either have to write code that retrieves the SQL associated with each
query and parses out the fields and tables (a non-trivial task...), or you
can purchase a 3rd party product like Total Access Analyzer, from FMS
http://www.fmsinc.com/products/analyzer/index.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"rj_budz" wrote in message
...
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!

If you go into immediate mode and type in something like the following
you'll se how to condtruct the looping to 'enumerate' you queries

? currentDb.QueryDefs.Count
30 [the go from 0 to this so you need to loop 0 to 29]

? currentDb.QueryDefs(29).Name
Query2 [this happened to be my last one
~sq_fProviders [this was number 13 and you will need to bypass these -
they're system queries rather than your own]
AssessmentList [this was number 14 - the first of my own defined queries]


? currentDb.QueryDefs(13).Fields.Count [gets the number of columns for query
number 13
31

? currentDb.QueryDefs(13).Fields(0).Name [gets the name of each column in
the query
Provider.aaa_Provider_id

Now! you're going to have a problem with columns which have been renamed
with AS xxxxx
If there aren't any you may be ok! you could store all the results in a
table
then look see which of the columns in the 2 linked tables are being used
(in which queries)

ALTERNATIVELY
you could use
? currentDb.QueryDefs(14).SQL
SELECT [Assessment].[aaa_Assessment_id], [Assessment].[ParticipantId],
Format([Assessment].[aDate],"d mmm yy") AS AssDate,
Format([Assessment].[TimeOfAsessment],"Short Time") AS AssTime,
[Provider].[prFirst_name] & " " & [Provider].[prLast_name] & " (" &
[Provider].[prNickName] & ")" AS AsessorFullName, [Participant].[IsChild]
FROM Provider INNER JOIN (Participant INNER JOIN Assessment ON
[Participant].[aaa_Participant_id]=[Assessment].[ParticipantId]) ON
[Provider].[aaa_Provider_id]=[Assessment].[AssessorId]
WHERE ((([Assessment].[ParticipantId])=[forms]![Participants2]![SrchId]))
ORDER BY [Assessment].[aDate];

then, in code get each column reference between SELECT and FROM
This way you'll see the 'AS's - and be able to do a select to pull us a
list.

HOWEVER - if the Front end Code generates sql using the tables concerned it
may be that you're using columns form the tables that aren't used in ANY of
your queries!

If you've got the idea of the above yopu're probably well on the way to
doing a coded search through your code to find any such occurences


AND THE BEST OF LUCK!!




"rj_budz" wrote in message
...
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!

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
--
-Larry-
--

"rj_budz" wrote in message
...
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!

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!

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.