Where Used Tool to Find Queries That Reference a Query/Table

[Dennis W. Bulgrien]

Has anybody written a macro or VBA code that finds the first/next query or all
queries that reference (use in its FROM clause) a given query or table name?

 

[Joe Fallon]

Lots of companies sell such a tool:
FMS
Speed Ferret (Black Moshanon?)

There may be a free utility called Find Replace.
Try a Google search.

 

[Douglas J. Steele]

FMS Total Access Analyzer:
http://www.fmsinc.com/products/analyzer/index.html
Find & Replace: http://www.rickworld.com/products.html
Speed Ferret: http://www.moshannon.com/speedferret.html

And Find & Replace isn't free: it's shareware that you're only supposed to
use for 30 days without registering. Depending on what version of Access you
need it for, registration costs between $15 and $37 per copy, and worth
every penny.

 

[Dennis W. Bulgrien]

Okay then. Here is one I just wrote that anybody is welcome to use.

' Desc: Searches all queries in current database for a requested text
Sub QueryWhereUsed()
Dim sText As String
Dim sQueries As String
Dim i As Integer
Dim qdf As QueryDef

sText = InputBox("SQL Text?")
For Each qdf In CurrentDb.QueryDefs
i = InStr(qdf.SQL, sText)
If i Then
MsgBox Left$(qdf.SQL, i - 1) & Space$(8) & sText & Space$(8) &
Mid$(qdf.SQL, i + Len(sText)), , qdf.Name
sQueries = sQueries & qdf.Name & vbCr
End If
Next qdf
MsgBox sQueries & vbCr & "End of search.", , "Query Where Used"
End Sub

"Dennis W. Bulgrien" <dbulgrien vcsd com> wrote in message
Has anybody written a macro or VBA code that finds the first/next query or all
queries that reference (use in its FROM clause) a given query or table name?