| | |
|
|
|
What queries are using a certain table? |
|---|
| message from AZEYE on 20 May 2004 |
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?
|
| Dirk Goldgar replied to AZEYE on 20 May 2004 |
"AZEYE" <anonymous@discussions.microsoft.com> wrote in message
news:fadc01c43e7b$408c1bc0$a601280a@phx.gbl
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 -----
|
| Larry Daugherty replied to Dirk Goldgar on 20 May 2004 |
Also, Find And Replace from Rick Fisher, www.rickworld.com will do it for
sure and maybe Speed Ferret.
|
| Les replied to AZEYE on 20 May 2004 |
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.
|
|
Archived message: What queries are using a certain table? (MS Access Database)
