List of Reports / Queries

[Parra]

Is there a way to get a list of the reports and queries in a database. I have to modify 20 reports and need to match them to the queries they are linked to and don't want to write this stuff out.

Any Suggestions, Thanks.

 

[Russell Hauf]

Hi Parra,

This is one way of doing what you want. You need to set a reference to:

Microsoft ADO Ext. 2.1 for DDL and Security

Option Explicit

Sub ListReportsAndQueries()
    Dim rpt As AccessObject
    
    Debug.Print "---------REPORTS---------"
    For Each rpt In CurrentProject.AllReports
        Debug.Print rpt.Name
    Next rpt
    
    Debug.Print

    
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    
    Debug.Print "---------QUERIES---------"
    For Each tbl In cat.Tables
        If tbl.Type = "VIEW" Then
            Debug.Print tbl.Name
        End If
    Next tbl
        
End Sub

HTH,

Russell

 

[Parra]

Hi Russell, uuummmm how do I use the code you provided me with? I know Access, but I am not a pro. Where do I put it? Module, Macro?

Thanks

 

[Russell Hauf]

Sorry! Just put it in a new macro. It will print to the immediate window in the VBE. Are you using Access 2000 or above?

 

[Parra]

Access 1997

Only management has the newer version or the computer requirements able to run it.

 

[Russell Hauf]

Ok, then try something like this:

Option Explicit

Sub ListReportsAndQueries97()

    Dim db As Database
    Dim ctr As Container
    Dim lngI As Long
    
    Set db = CurrentDb
    Set ctr = db.Containers("Reports")
    
    Debug.Print "---------REPORTS---------"
    For lngI = 0 To ctr.Documents.Count - 1
        Debug.Print ctr.Documents(lngI).Name
    Next lngI
    
    Debug.Print
    
    Dim qdf As QueryDef

    Debug.Print "---------QUERIES---------"    
    For Each qdf In db.QueryDefs
        ' Probably don't want to print the hidden queries
        If Left$(qdf.Name, 4) <> "~sq_" Then
            Debug.Print qdf.Name
        End If
    Next qdf
        
End Sub

HTH,

Russell