Brook,
If you're working with A2K3 (not sure about A2K2, but probably the same
holds true there) you are lucky, it offers the "object dependencies"
functionality; just right-click on a table or query, and select this option.
If you are using an older version, things are not that simple, but still
workable; paste the code below in a general module:
Sub Export_Table_Dependencies()
Dim vPath, vName
vPath = CurrentProject.Path
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open vPath & "\" & vName & " Table Dependencies.txt" For Output As #1
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
For Each qry In CurrentDb.QueryDefs
If InStr(1, qry.SQL, tbl.Name) > 1 Then
Print #1, tbl.Name & " is used in query " & qry.Name
End If
Next
End If
Next
Close #1
End Sub
Sub Export_Query_Dependencies()
Dim vPath, vName
vPath = CurrentProject.Path
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open vPath & "\" & vName & " Query Dependencies.txt" For Output As #1
For Each qry In CurrentDb.QueryDefs
If Left(tbl.Name, 4) <> "MSys" Then
For Each qry1 In CurrentDb.QueryDefs
If InStr(1, qry1.SQL, qry.Name) > 1 Then
Debug.Print qry.Name & " is used in query " & qry1.Name
End If
Next
End If
Next
Close #1
End Sub
And run both subs; each will create a text field in the same directory
as the database, with lines like:
TableX is used in query QueryY
etc.
and
Query1 is used in query Query2
etc.
They will also catch SQL expressions in the rowsource of list boxes or
combo boxes in forms (where the query name starts with "~sq_"), but they
won't catch SQL expressions in forms' or reports' RecordSource property
that are not saved queries, so you need to be careful there.
HTH,
Nikos
