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

Access System Tables

While working on a project with hundreds of reports, each with a query, I wanted a list of each report along with its query. I found out two things. Hidden tables such as MSysObjects and programatically accessing Object dependencies.
I will talk about the hidden tables first.
1. Under 2007, go to the Office button in the upper-left corner of Access.
2. Select Access Options.
3. Select Current Database.
4. Under Navigation, select Navigation Options.
5. Check Show System Objects.

Now when creating a query, you have simple access to tables such as MSysOjects where you can list objects like reports or queries.

To list all the report names, I used

SELECT Name FROM MSysObjects WHERE Not Like "~*" And Not Like "MSys*" AND Type = "-32764"

This lists only reports and excludes system reports.

Under MSysQueries, you can list query expressions.

SELECT MsysObjects.Name, MSysQueries.Expression
FROM MSysQueries INNER JOIN MsysObjects ON MSysQueries.ObjectId = MsysObjects.Id
WHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*") AND ((MSysQueries.Attribute)=8) AND ((MsysObjects.Type)=5));

This lists the queries and the criteria. You can list much more.
These system tables do not list what report uses what query. That is where dependencies come in.