Solved Searching query text. (1 Viewer)

[John Sh]

Is there an easy way to search the text of multiple queries for a particular string?
I have a situation where a query is requesting input but I can't track down which query is the culprit.

 

[tvanstiphout]

Sub dumpQueriesWithSQL(ByVal sql As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.sql Like "*" & sql & "*" Then Debug.Print qd.Name
Next qd
End Sub

 

[John Sh]

I found a sub by "GUUS" in 2007 that fills the bill nicely.

Search for a text trough all queries.

Here's a function which searches for a string or a number of strings in all queries. Open the immediate window and enter: ?SearchInQueryDefs2 "INT", "OUT", "DO" The results will be displayed in the messagebox. Tell me what you think. Enjoy! Public Sub SearchInQueryDefs2(ParamArray...

www.access-programmers.co.uk

It scrolls through al qdf's and returns each query with the search string in a message box.

 

[John Sh]

Sub dumpQueriesWithSQL(ByVal sql As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.sql Like "*" & sql & "*" Then Debug.Print qd.Name
Next qd
End Sub

Thanks Tom. It certainly does what is required. The other sub lists the query code as well so is a bit more versatile.
Thank you for responding.
John

 

[John Sh]

Sub dumpQueriesWithSQL(ByVal sql As String)
Dim db As DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
For Each qd In db.QueryDefs
If qd.sql Like "*" & sql & "*" Then Debug.Print qd.Name
Next qd
End Sub

I have altered you sub to add the found query names to a table. This makes it easier to check the queries at a later time.
John

Private Sub dumpQueriesWithSQL(ByVal sql As String)
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM qtemp"
        Set db = CurrentDb
        For Each qd In db.QueryDefs
            If qd.sql Like "*" & sql & "*" Then DoCmd.RunSQL "INSERT INTO [qtemp] ([query]) VALUES ('" & qd.Name & "' );"
        Next qd
    DoCmd.SetWarnings True
End Sub

 

[John Sh]

I have taken this a step further and created a form that searches all queries for the given text then prints out the text of the query into a textbox.
Select any query from the dropdown to see it's code.
I have left popup and modal as no., allowing the queries to be accessed while the form is still active.
Hopefully this will help someone else with my predicament.
John