Sys table Query to Obtain List of Queries used in Reports

[Brad]

Is it possible to query the Access 2007 Sys tables to obtain a list of which
queries are used in which reports?

Thanks,
Brad

 

[Arvin Meyer [MVP]]

If you've named your queries according to their functionality it is easy. To
get a list of queries from the system table use:

SELECT Name, DateCreate, DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=5));

The quewry has no idea where it will be used so there isn't any way for it
to give you that information. The recordsource is in code or it's name is
stored with the form or report that calls it.

 

[Brad]

Arvin,

Thanks for your help.

After reading my original question again, I think that I did not explain
what I would like to do very well.

We have lots of Access Reports. All reports use Queries as their record
source.

I would like to be able to obtain a list of all reports and show their
record source (in our case, this would be a query name)

Here is a small example -



REPORT Record-Source (Query)

Report001 Query543
Report002 Query847
Report003 Query093
Report004 Query938

I know that I can obtain this info via the Database-Documenter but I would
like to have a much more concise report.

Thanks,
Brad





Brad

 

[Jerry Whittle]

Rich Fisher's excellent Find and Replace add-in might be just the ticket. If
you register it (one of the best $37 I've ever spent), the cross-reference
report can find what queries use which reports and forms. It's a good way to
find orphans.

http://www.rickworld.com/

 

[Dirk Goldgar]

Arvin,

Thanks for your help.

After reading my original question again, I think that I did not explain
what I would like to do very well.

We have lots of Access Reports. All reports use Queries as their record
source.

I would like to be able to obtain a list of all reports and show their
record source (in our case, this would be a query name)

Here's a quick and dirty procedure you could use:

'------ start of code ------
Sub ListReportRecordSources()

' Search the recordsources of all reports
' for the specified string.

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim doc As DAO.Document

Dim lngReportCount As Long

Debug.Print "*** Beginning scan ..."

Set db = CurrentDb
For Each doc In db.Containers("Reports").Documents
DoCmd.OpenReport doc.Name, acDesign, WindowMode:=acHidden
With Reports(doc.Name)
lngReportCount = lngReportCount + 1
Debug.Print "Report " & .Name & " RecordSource: " &
..RecordSource
DoCmd.Close acReport, .Name
End With
Next doc

Exit_Point:
Set doc = Nothing
Set db = Nothing
Debug.Print "*** Scanned " & lngReportCount & _
" reports."
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'------ end of code ------

Watch out for lines that may have been wrapped by the newsreader.

 

[Dirk Goldgar]

Minor correction: I should have changed the procedure's header comment to
reflect the way I cut it down. This:

' Search the recordsources of all reports
' for the specified string.

.... should have been something like this:

' List the recordsources of all reports.

Sorry about the oversight.

 

[Brad]

Dirk,

Thanks much, I appreciate your help!

Brad

~~~~~~~~~~~~~~~~~~

 

[Marco Pagliero]

I would like to be able to obtain a list of all reports and show their
record source (in our case, this would be a query name)
Here is a small example -  
REPORT       Record-Source (Query)
Report001    Query543
Report002    Query847
Report003    Query093
Report004    Query938

Sub ReportRecordSource()
Dim rpt As Report
Dim obj As AccessObject
Open "C:\Test.txt" For Output As #1

For Each obj In CurrentProject.AllReports
DoCmd.OpenReport obj.Name, acDesign
Set rpt = Reports(obj.Name)
Print #1, rpt.Name, rpt.Properties(0)
DoCmd.Close acReport, rpt.Name, acSaveNo
Next

Close
Set rpt = Nothing
Set obj = Nothing

End Sub

Regards
Marco P

 

[Brad]

Marco,

Thanks, I appreciate your assistance.

Brad