SearchInQueryDefs2 (from Guus2005)

[Les Isaacs]

Hi All

I have been using the 'SearchInQueryDefs2' code from Guus2005 in the Code Repository, and it's excellent - thank you Guus2005

I was hoping to use it to help me amend some joins, because I'm replacing some text key fields with numeric key fields. I can use the 'SearchInQueryDefs2' code as it is to find the relevant joins, but I'd like to adapt it so that I can replace the search string with a new string. Does anyone know whether this can be done - or whether there is another utility to find and replace strings in query definitions?

Thanks for any help.
Les

 

[CJ_London]

don't think I can answer the question, but you are more likely to get a response if you reproduce the code here and/or provide a link to the thread with the code.

 

[Les Isaacs]

Good point!

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

 

[jdraw]

Les,

There is a free tool called v-tools that may be able to do what you ask.

IT is my understanding that queries starting with "~" were "logically deleted" and would be purged with Compact and Repair (my understanding may be incorrect).

I don't think the code you showed will work IF there are queries/joins built on the fly with vba--but that may not be your situation.

Good luck.

 

[static]

To find a string in another string

InStr(TheText, TextToFind)

To replace part of a string with another string

Replace(TheText, TextToFind, NewText)

To set the SQL of a querydef

qd.SQL = "select * from table1"

To cycle through all querydefs

For Each qd In CurrentDb.QueryDefs

next

So to replace all uses of, say table1 with table2...

Private Sub Command0_Click()
    Dim qd As QueryDef
    
    For Each qd In CurrentDb.QueryDefs
        ReplaceSQL qd, "table1", "table2"
    Next
End Sub

Public Sub ReplaceSQL(qd As QueryDef, fnd As String, rep As String)
    If InStr(qd.SQL, fnd) Then
        qd.SQL = Replace(qd.SQL, fnd, rep)
    End If
End Sub