database documentation

  • Thread starter Thread starter EJ
  • Start date Start date
E

EJ

Is there a simple way to find all queries that reference a particular table
without opening them up one by one?
 
Is there a simple way to find all queries that reference a particular table
without opening them up one by one?
Click to expand...

Copy and Past the below code into a module:

Public Sub FindTables()
' find queries that include a specific table in it's recordsource.
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
For Each qry In db.QueryDefs
If Left(qry.Name, 1) <> "~" Then
strSQL = qry.Properties("SQL")
If InStr(strSQL, "TheTableName") > 0 Then
Debug.Print qry.Name
End If
End If
Next
End Sub

Change "TheTableName" to whatever the exact name of the table you are
searching for is.
 
Also can click on the table and then View->Object Dependencies

If Autocorrect is partially turned on
 
Per EJ:
Is there a simple way to find all queries that reference a particular table
without opening them up one by one?
Click to expand...

Yes and no.

As others have observed, just beating through the objects list
can do it... sort of.

But the few methods I've tried (homegrown and purchased) broke
down when it came to nested queries. e.g. qryWhatever
references tblWhatever and qryDoobie uses qryWhatever....
 
Per (PeteCresswell):
As others have observed, just beating through the objects list
can do it... sort of.

But the few methods I've tried (homegrown and purchased) broke
down when it came to nested queries. e.g. qryWhatever
references tblWhatever and qryDoobie uses qryWhatever....
Click to expand...

Actually, my homegrown approach dealt with nested queries to some
extent... but when it came to work tables being created
on-the-fly, I gave up.
 
Back
Top