Create A List of Dependencies

I had been racking my brain trying to get Microsoft Access to tell me what Query corresponds with each Report. It is defined in each Report property RecordSource. This is not stored in the system tables MSysObjects or MSysQueries.
I came up with the solution programmatically by defining variables as AccessObjects and using a For Next loop to go through each Report and nest another loop to go through each Dependency.
In my example, I store this information into a table. I also included to blocks of code to handle apostrophies in the names of the reports and dependencies. The extra single ' plays havoc on the SQL execution because the single ' is used to define SQL statements. (i.e. SELECT * FROM myTable WHERE somefield = 'somevalue' )

Sub CreateListOfReports()

    Dim rpt As AccessObject     ' report object
    Dim rptdep As AccessObject  ' report dependency object
    Dim mySQL As String         ' SQL to insert report name and dependency into a table.
    Dim strReport As String     ' report name
    Dim strReportDep            ' report dependency name
    
    On Error GoTo Err_CreateListOfReports
    
    Application.SetOption "Track Name AutoCorrect Info", 1
    For Each rpt In CurrentProject.AllReports   ' go through each report
        strReport = rpt.NAME                    ' get report name
        DoCmd.SetWarnings False                 ' appending a row to a table so turn off warning prompt.
        
        ' I added this because some of the report names had the apostrophy which caused errors in the SQL execution.
        ' A report named, "Director's Report" would cause an error because the SQL would consider the ' as part of the SQL.
            s = 1
            While s < Len(strReport) And InStr(s, strReport, "'", vbTextCompare)  ' while there is a single ' in the name
                ' find where the single ' is in the report name
                s = InStr(s, strReport, "'", vbTextCompare)
                ' for any single ' in the name, make it become '' to work in the SQL statment.
                strReport = Left(strReport, s) + "'" + Right(strReport, Len(strReport) - s)     ' join the left and right of the report name
                s = s + 2   ' skip past the second ', don't want to mistake the '' for a single.
            Wend
            
            For Each rptdep In rpt.GetDependencyInfo.Dependencies   ' for each report, list each dependency.
            strReportDep = rptdep.NAME                              ' get the dependency name
            Debug.Print strReport, strReportDep                     ' I like to list each in the debugger window.
            
            ' repeat the same for the dependency name when dealing with single '
            ' otherwise, the SQL generates an error when executing.
            s = 1
            While s < Len(strReportDep) And InStr(s, strReportDep, "'", vbTextCompare) ' while there is a single ' in the dependency name
                s = InStr(s, strReportDep, "'", vbTextCompare)  ' find where the single ' is in the name
                strReportDep = Left(strReportDep, s) + "'" + Right(strReportDep, Len(strReportDep) - s) ' replace the single ' with the double ''
                s = s + 2   ' skip the '' you just added.
            Wend
            ' insert the report name and its dependency into the table.
            mySQL = "INSERT INTO tblListReportsAndDependencies (Report, Dependency) VALUES ('" + strReport + "', '" + strReportDep + "')"
            DoCmd.RunSQL mySQL
        Next
    Next
    DoCmd.SetWarnings True                      ' turn on warning prompt
    Exit Sub
    
Err_CreateListOfReports:
    MsgBox Error(Err), vbCritical, "Error " & Err
    
End Sub

Download getdependenciesmodule-bas