Search queries for specific field usage

[KimmieB]

I've just inheirited a large access db and I was wondering if there was a way to search the system tables for usage of a specific table\field name in a query. I'v done this with SQL and the syscomments table but access doesn't appear to have an equivilant system table.

 

[Remou]

Hi
Have you looked at the MySysQueries table (answer due to RoyVidar see thread702-839833)?

 

[KimmieB]

That table won't work. Unlike sys comments in SQL which has the complete text of the stored proc so you can search on a string value, it looks like msysqueries doesn't have the same. I think what I need is a table that contains the SQL view of queries if that makes sense.

 

[Remou]

Hi
Would this work:

Dim qdf As QueryDef
Dim rs As Recordset
Dim strMyTable

strMyTable = "Table1"
'Choose only queries
Set rs = CurrentDb.OpenRecordset("SELECT MSysObjects.Name FROM MSysObjects " _
& "WHERE MSysObjects.Type=5 And left$(name,1)<>'~'")
rs.MoveFirst
Do While Not rs.EOF()
    Set qdf = CurrentDb.QueryDefs(rs!Name)
    If InStr(1, qdf.sql, strMyTable) > 0 Then
        Debug.Print qdf.sql
    End If
    rs.MoveNext
Loop
End Sub

It could be used to create a table.

 

[RoyVidar]

I think you're right, the sql of the queries can probably not be found in the system tables. Here's a go at looping the querydefs collection:

[tt]public function GetQueryNames( _
byval v_strField as string) as string
dim qd as dao.querydef
dim strReturn as string
for each qd in currentdb.querydefs
if (left$(qd.name,1) <> "~")) then
if (instr(qd.sql, v_strField) > 0) then
strReturn = strReturn & qd.name & vbnewline
end if
end if
next qd
set qd = nothing
GetQueryNames = strReturn
end function[/tt]

Could be called from the immediate pane with:

[tt]? GetQuerynames("Mytable.Myfield")[/tt]

Should be easy to build on - but - be aware that the table/field may bee in use lots of places outside queries. Dynamic recordsources for forms and reports, rowsources for combos/listboxes, dynamic sql in code... you may want to investigate even further. If you use the documenter in Tools | Analyze, you can probably export that to a searchable textfile, or you could for instance dump all your objects to searchable textfiles through the method described here thread705-751080. Then search manually, or write a small routine to go through the files.

Roy-Vidar