Prev: Access 2007 Developer Tools and SourceSafe
Next: No Field Edit capability
From: Brad on 13 May 2010 17:02
Is it possible to query the Access 2007 Sys tables to obtain a list of which
queries are used in which reports?

Thanks,
Brad
From: Arvin Meyer [MVP] on 13 May 2010 20:38
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" <Brad(a)discussions.microsoft.com> wrote in message
news:C9CC5D9D-5F28-4AF5-A1BB-334FDA52169F(a)microsoft.com...
> Is it possible to query the Access 2007 Sys tables to obtain a list of
> which
> queries are used in which reports?
>
> Thanks,
> Brad


From: Brad on 14 May 2010 06:37
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" <Brad(a)discussions.microsoft.com> wrote in message
> news:C9CC5D9D-5F28-4AF5-A1BB-334FDA52169F(a)microsoft.com...
> > Is it possible to query the Access 2007 Sys tables to obtain a list of
> > which
> > queries are used in which reports?
> >
> > Thanks,
> > Brad
>
>
> .
>
From: Jerry Whittle on 14 May 2010 10:19
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" <Brad(a)discussions.microsoft.com> wrote in message
> > news:C9CC5D9D-5F28-4AF5-A1BB-334FDA52169F(a)microsoft.com...
> > > Is it possible to query the Access 2007 Sys tables to obtain a list of
> > > which
> > > queries are used in which reports?
> > >
> > > Thanks,
> > > Brad
> >
> >
> > .
> >
From: Dirk Goldgar on 14 May 2010 11:02
"Brad" <Brad(a)discussions.microsoft.com> wrote in message
news:61F6F5C0-AA7E-48DE-B983-090A91C1A0D7(a)microsoft.com...
> 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)

 |  Next  |  Last
Pages: 1 2
Prev: Access 2007 Developer Tools and SourceSafe
Next: No Field Edit capability