Searching All Database Objects - Microsoft Access

Overview¶

• SQL statements in Query objects
• The RecordSource of every Form object, and every ComboBox on a Form
• The RecordSource of every Report object
  

Limitations¶

• No code (module objects) are searched
• No macros are searched
• No form controls (except for ComboBoxes) are searched
  

Code¶

'==================================================================================================
Public Sub ExamineAllObjects()

    Const lookFor As String = "my text here"
    
    Debug.Print "====================================================================="

    ExamineAllQueries lookFor
    ExamineAllForms lookFor
    ExamineAllReports lookFor

    Debug.Print "====================================================================="
    Debug.Print "====================================================================="

End Sub
'==================================================================================================
Public Sub ExamineAllQueries(lookFor As String)

    Dim imin As Long
    Dim imax As Long
    Dim i As Long
    Dim sSql As String
    Dim lookForUpper As String

    With DBEngine.Workspaces(0).Databases(0)
    
        imin = 0
        imax = .QueryDefs.Count - 1 + imin
        lookForUpper = UCase$(lookFor)
        Debug.Print "====================================================================="
        Debug.Print "  The SQL for the following queries contain '" & lookFor & "'"
        Debug.Print "---------------------------------------------------------------------"
        For i = imin To imax
        
            sSql = UCase$(.QueryDefs(i).sql)
            
            If sSql Like "*" & lookForUpper & "*" Then
            
                Debug.Print .QueryDefs(i).Name
                
            End If
            
        Next
        Debug.Print "---------------------------------------------------------------------"
    
    End With

End Sub
'==================================================================================================
Public Sub ExamineAllReports(lookFor As String)

    Dim obj As Report
    Dim imin As Long
    Dim imax As Long
    Dim i As Long
    Dim lookForUpper As String
    Dim objectName As String
    Dim rs As String
    
    imin = 0
    imax = CurrentProject.AllReports.Count - 1 + imin
    lookForUpper = UCase$(lookFor)
    
    Debug.Print "====================================================================="
    Debug.Print "  The following reports reference '" & lookFor & "'"
    Debug.Print "---------------------------------------------------------------------"
    
    For i = imin To imax
    
        objectName = CurrentProject.AllReports(i).Name
        DoCmd.OpenReport objectName, acViewDesign
        Set obj = Application.Reports(objectName)
        rs = UCase$(obj.RecordSource)
        DoCmd.Close acReport, objectName, acSaveNo
        
        If rs Like "*" & lookForUpper & "*" Then
            Debug.Print objectName
        End If
    
    Next
    
    Debug.Print "---------------------------------------------------------------------"

End Sub
'==================================================================================================
Public Sub ExamineAllForms(lookFor As String)

    Dim obj As Form
    Dim imin As Long
    Dim imax As Long
    Dim i As Long
    Dim lookForUpper As String
    Dim objectName As String
    Dim rs As String
    Dim ctl As Control
    Dim cbo As ComboBox
    
    imin = 0
    imax = CurrentProject.AllForms.Count - 1 + imin
    lookForUpper = UCase$(lookFor)
    
    Debug.Print "====================================================================="
    Debug.Print "  The following forms reference '" & lookFor & "'"
    Debug.Print "---------------------------------------------------------------------"
    
    For i = imin To imax
    
        objectName = CurrentProject.AllForms(i).Name
        DoCmd.OpenForm objectName, acViewDesign
        Set obj = Application.Forms(objectName)
        
        For Each ctl In obj.Controls
            If ctl.ControlType = acComboBox Then
                Set cbo = ctl
                If UCase$(cbo.RowSource) Like "*" & lookForUpper & "*" Then
                    Debug.Print objectName & " > ComboBox: " & cbo.Name
                End If
            End If
        Next
        
        rs = UCase$(obj.RecordSource)
        DoCmd.Close acForm, objectName, acSaveNo
        
        If rs Like "*" & lookForUpper & "*" Then
            Debug.Print objectName
        End If
    
    Next
    
        Debug.Print "---------------------------------------------------------------------"

End Sub