Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Dependencies in Microsoft Access

Overview¶

The following Access VBA code will look for references to the specified text within all queries (SQL code), forms (RecordSource or ComboBox.RecordSource), reports (RecordSource), and modules (source code). To use this code, create a new Module, call it "basDependencies", and paste this code into it.

Code¶

Option Compare Database
Option Explicit

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

    Const lookFor As String = "tblTEMPTransVW"
    
    Debug.Print "====================================================================="

    ExamineAllQueries lookFor
    ExamineAllForms lookFor
    ExamineAllReports lookFor
    ExamineAllModules 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 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
'==================================================================================================
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 ExamineAllModules(lookFor As String)

    Dim obj As Module
    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 lineQty As Long
    
    imin = 0
    imax = CurrentProject.AllModules.Count - 1 + imin
    lookForUpper = UCase$(lookFor)
    
    Debug.Print "====================================================================="
    Debug.Print "  The following modules reference '" & lookFor & "'"
    Debug.Print "---------------------------------------------------------------------"
    
    For i = imin To imax
    
        objectName = CurrentProject.AllModules(i).NAME
        
        ' Don't search the module this code is in, "basDependencies"
        If objectName <> "basDependencies" Then
            DoCmd.OpenModule objectName
            Set obj = Application.Modules(objectName)
            lineQty = obj.CountOfLines
            rs = UCase$(obj.Lines(1, lineQty))
            DoCmd.Close acModule, objectName, acSaveNo
            
            If rs Like "*" & lookForUpper & "*" Then
                Debug.Print objectName
            End If
        End If
    
    Next
    
    Debug.Print "---------------------------------------------------------------------"

End Sub