which queries use table X?

Or:
You could use the Documenter to create a report that included the SQL code
for all your queries/reports, export the Documenter report to Word and then
do a "Find" on the desired table names within Word.

--
George Nicholson

Remove 'Junk' from return address.



"OldTymer" wrote in message
...
Is there a way to find out which queries refer to a given table?

I have to modify how tables X, Y and Z are used throughout this app. I
can
use FIND in the VB Editor to search all Forms and Modules in the whole
project for a table name -- but what about searching in queries? Or
reports?

Bonus question: is there an easy way to find the 'dead wood' in a
project?
I man things like unused queries, reports, tables, forms, etc. I can see
that quite a few people have carelessly hacked on this project over the
years, which makes it harder to "just fix" what I'm working on.

Thanks muchly.

"George Nicholson" wrote:
Or: You could use the Documenter to create a report that included the SQL code
for all your queries/reports, export the Documenter report to Word and then
do a "Find" on the desired table names within Word.

I liked this idea because I could spend the time once and search multiple
times more quickly.

Unfortunately it hung Access on a query called "QTest_Crosstab" -- high CPU
usage on MSACCESS.EXE for 10+ minutes on this 2.4GHz P4 with WinXP.

"OldTymer" wrote:
Unfortunately it hung Access...

And upon using the MS automated problem reporting, it notified me that this
machine needs Office SP3. Only a 48 minute download. Sigh.

"Ken Snell [MVP]" wrote:
Put this subroutine in a regular module, and then run it from the Immediate
Window (provide the table name as a text string as the argument). It'll
return a list of all queries that use that table.
:

Very slick! Worked like a charm. Thank you, Ken!

Thank Dan! This worked as well as Ken's previous post, in fact this version
creates identical output with the option of uncommenting the SQL:

Public Sub SearchInAllQueries(strKeyWord As String)
Dim qryDef As DAO.QueryDef
For Each qryDef In CurrentDb.QueryDefs
If InStr(1, qryDef.SQL, strKeyWord, vbTextCompare) And InStr(1,
qryDef.name, "~", vbTextCompare) = 0 Then
Debug.Print qryDef.name
' Debug.Print qryDef.SQL
End If
Next
End Sub

"gls858" wrote:
You don't mention what version of Access you are using but
in 2003 You ca n go to View Object Dependencies and a
window will open showing your tables, queries and reports.

2002. :-(

Thanks, tho.

"John Vinson" wrote:
Freewa http://www3.bc.sympatico.ca/starthere/findandreplace

Slow, but it seemed to do the trick! Plus free and easy to install and use.

Find and Replace: http://www.rickworld.com
30 day full eval, then $37

Speed Ferret: http://www.moshannon.com
$199 (very cute logo and site), semifunctional until registered

Total Access Analyzer: http://www.fmsinc.com
$299, demo version views only their demo project

On Tue, 15 Mar 2005 13:23:06 -0800, "OldTymer"
wrote:

"John Vinson" wrote:
Freewa http://www3.bc.sympatico.ca/starthere/findandreplace

Slow, but it seemed to do the trick! Plus free and easy to install and use.

Find and Replace: http://www.rickworld.com
30 day full eval, then $37

Speed Ferret: http://www.moshannon.com
$199 (very cute logo and site), semifunctional until registered

Total Access Analyzer: http://www.fmsinc.com
$299, demo version views only their demo project

I use (and like) both Speed Ferret and Total Access Analyzer. For
heavy-duty development they're both worth the investment IMHO - and
though they overlap some in functionality, they're enough different
that I do use both of them.

John W. Vinson[MVP]