What queries are using a certain table?

  • Thread starter Thread starter AZEYE
  • Start date Start date
A

AZEYE

Is there a way to find what queries are using a specific
table? I have a database with over 100 queries and I need
to find out which ones are using a specific table. Is
there anyway to do that besides going through each query?
 
I go to tools|analyze|documenter, select all queries, run
report, then copy the report to a text file. I use find
command in ultra edit to locate the table, and identify
the querys that use the table.
 
AZEYE said:
Is there a way to find what queries are using a specific
table? I have a database with over 100 queries and I need
to find out which ones are using a specific table. Is
there anyway to do that besides going through each query?
Click to expand...

It's not exactly what you asked for, but here's a little routine to
search all the stored queries for a particular string (which might be a
table name) in their SQL:

'----- start of code -----
Sub SearchQueries(strSought As String)

' Search all queries for SQL containing the specified string.

On Error GoTo Err_SearchQueries

Dim db As DAO.Database
Dim qdf As QueryDef

Dim varTest As Variant
Dim lngSearchCount As Long
Dim lngFoundCount As Long

Debug.Print "*** Beginning search ..."

Set db = CurrentDb
For Each qdf In db.QueryDefs
With qdf
If Left$(.Name, 3) <> "~sq" Then
lngSearchCount = lngSearchCount + 1
If InStr(.SQL, strSought) Then
Debug.Print "Query " & .Name & " SQL: " & .SQL
lngFoundCount = lngFoundCount + 1
End If
End If
End With
Next qdf

Exit_SearchQueries:
Set qdf = Nothing
Set db = Nothing
Debug.Print "*** Searched " & lngSearchCount & _
" objects, found " & lngFoundCount & " occurrences."
Exit Sub

Err_SearchQueries:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_SearchQueries

End Sub
'----- end of code -----
 
Back
Top