Search for a text trough all queries. (1 Viewer)

Status
Not open for further replies.

Guus2005

AWF VIP
Local time
Today, 10:35
Joined
Jun 26, 2007
Messages
2,645
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!
Code:
Public Sub SearchInQueryDefs2(ParamArray arrSearch())
'Search for all strings in the paramarray in all queries.
'Run from intermediate window.
'
'Example: SearchInQueryDefs2 "INT", "OUT", "DO"
'
   Dim qdf      As QueryDef
   Dim qdfS     As QueryDefs
   Dim blnFound As Boolean
   Dim intSearch As Integer
   Dim intCount As Integer
   Dim strFound As String
   Dim intCtl    As Integer

   On Error GoTo Err_SearchInQueryDefs2

   Set qdfS = CurrentDb.QueryDefs

   intSearch = UBound(arrSearch, 1)
   
   For Each qdf In qdfS
      For intCount = 0 To intSearch
         blnFound = InStr(1, qdf.SQL, arrSearch(intCount)) > 0
         If Not blnFound Then
            Exit For 'Not found, exit loop, continue next query
         End If
      Next intCount
      If blnFound Then
         Select Case Left$(qdf.Name, 5)
         Case "~sq_f" ' rowsource form
             strFound = "Form: " & Mid$(qdf.Name, 6) & vbCrLf
         Case "~sq_c" ' rowsource control on form
             intCtl = InStr(1, Mid$(qdf.Name, 2), "~")
             strFound = "Form   : " & Mid$(qdf.Name, 6, intCtl - 5) & vbCrLf & "Control: " & Mid$(qdf.Name, intCtl + 6) & vbCrLf
         Case Else 'query
             strFound = "Query: " & qdf.Name & vbCrLf
         End Select
         
         Debug.Print "Found string(s) in: " & qdf.Name
         If vbNo = MsgBox("String(s) found in " & vbCrLf & vbCrLf & strFound & vbCrLf & qdf.SQL & vbCrLf & vbCrLf & "Confirm to continue the search, 'No' to stop", vbExclamation + vbYesNo, "SearchInQueryDefs") Then
            Exit Sub
         End If
      End If
   Next qdf

   MsgBox "Done searching.", vbInformation

Exit_SearchInQueryDefs2:
    Exit Sub

Err_SearchInQueryDefs2:
   MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure SearchInQueryDefs2 of Module modUtility"
    Resume Exit_SearchInQueryDefs2
Resume 'For debugging purposes

End Sub
 

ELF_VIGO

New member
Local time
Today, 02:35
Joined
Oct 20, 2015
Messages
1
Thank you very much. I have to search in queries "Formulaires" in order to substitute by "Forms" (An Access aplication from French version of Access...).
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom