Page History: Searching All Database Objects - Microsoft Access

Compare Page Revisions

2 1 0 Current 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Sat, Oct 06, 2012, 12:31 PM

Overview¶

The following code searches all code objects (with limitations - see below) for references to a specified text string.

Limitations¶

• No code (module objects) are searched
• No macros are searched
• No form controls (e.g., dropdown lists) 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
    
    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)
        rs = UCase$(obj.RecordSource)
        DoCmd.Close acForm, objectName, acSaveNo
        
        If rs Like "*" & lookForUpper & "*" Then
            Debug.Print objectName
        End If
    
    Next
    
    Debug.Print "---------------------------------------------------------------------"

End Sub