This is a piece of code that I use in virtually all of my MS Access projects. Basically, the code allows you to search all the queries in a database project for a particular string. After identifying all the queries that contain the string, it prints them out to the VBE Immediate Window for your reference.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Option Explicit Option Compare Database Public Sub FindTextWithinQueries() On Error GoTo ERROR_HANDLER: Dim qdf As DAO.QueryDef Dim db As DAO.Database Dim strSearchValue As String Dim strResults As String strSearchValue = InputBox("Enter the text you want to search for in the database queries", "Search") If Len(Trim(strSearchValue)) = 0 Then MsgBox "No search string entered!", vbExclamation, "Abort" GoTo ExitHere: End If Debug.Print "Results of search for '" & strSearchValue & "' follow:" Set db = CurrentDb() For Each qdf In db.QueryDefs If InStr(1, qdf.SQL, strSearchValue, vbTextCompare) <> 0 Then strResults = strResults & vbCrLf & qdf.Name Debug.Print qdf.Name End If Next qdf MsgBox "Queries containing the string '" & strSearchValue & "' are as follows:" & vbCrLf & _ "(full results can be viewed in the code immediate window)" & vbCrLf & strResults, vbInformation + vbOKOnly, "Search Results" EXIT_HERE: Set qdf = Nothing Set db = Nothing Exit Sub ERROR_HANDLER: Debug.Print Err.Description GoTo ExitHere End Sub |
I hope you find it useful for your own projects!
Thank you for reading this post. Please take time to read the disclaimer about content found on this site.
Share :
