Page History: Dependencies in Microsoft Access

Compare Page Revisions

0 Current 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Tue, Sep 03, 2013, 12:59 PM

Overview¶

The following Access VBA code will look for references to the specified text within all queries (SQL code), forms (RecordSource or ComboBox.RecordSource), and reports (RecordSource).

Code¶

Option Compare Database
Option Explicit

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

    Const lookFor As String = "qrySummary_ProductForecastSchedule"
    
    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