Jasinski Technical Wiki

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

PoweredBy

Searching All Database Objects - Microsoft Access

RSS
Modified on Sat, Oct 06, 2012, 2:36 PM by Administrator Categorized as Microsoft Office

Overview

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

  • 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.