Sys table Query to Obtain List of Queries used in Reports

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

Thanks,
Brad

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Brad" wrote in message
...
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,

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


"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


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

Thanks,
Brad


.

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/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Brad" wrote:

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


"Arvin Meyer [MVP]" wrote:

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


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

Thanks,
Brad


.

"Brad" wrote in message
...
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, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

"Dirk Goldgar" wrote in message
...

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.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk,

Thanks much, I appreciate your help!

Brad

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


"Dirk Goldgar" wrote:

"Dirk Goldgar" wrote in message
...

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.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On 14 Mai, 12:37, Brad wrote:
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

Marco,

Thanks, I appreciate your assistance.

Brad


"Marco Pagliero" wrote:

On 14 Mai, 12:37, Brad wrote:
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
.