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
0
Utf
5/13/2010 9:02:15 PM
access 16762 articles. 3 followers. Follow

8 Replies
653 Views

Similar Articles

[PageSpeed] 11

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@discussions.microsoft.com> wrote in message 
news:C9CC5D9D-5F28-4AF5-A1BB-334FDA52169F@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 


0
Arvin
5/14/2010 12:38:34 AM
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@discussions.microsoft.com> wrote in message 
> news:C9CC5D9D-5F28-4AF5-A1BB-334FDA52169F@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 
> 
> 
> .
> 
0
Utf
5/14/2010 10:37:01 AM
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@discussions.microsoft.com> wrote in message 
> > news:C9CC5D9D-5F28-4AF5-A1BB-334FDA52169F@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 
> > 
> > 
> > .
> > 
0
Utf
5/14/2010 2:19:01 PM
"Brad" <Brad@discussions.microsoft.com> wrote in message 
news:61F6F5C0-AA7E-48DE-B983-090A91C1A0D7@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)

0
Dirk
5/14/2010 3:02:01 PM
"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:31FFA02D-0256-42DB-B557-1E6FE19C8CC6@microsoft.com...

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)

0
Dirk
5/14/2010 3:09:33 PM
Dirk,

Thanks much, I appreciate your help!

Brad

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


"Dirk Goldgar" wrote:

> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
> news:31FFA02D-0256-42DB-B557-1E6FE19C8CC6@microsoft.com...
> 
> 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)
> 
0
Utf
5/14/2010 5:48:07 PM
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 - =A0
> REPORT =A0 =A0 =A0 Record-Source (Query)
> Report001 =A0 =A0Query543
> Report002 =A0 =A0Query847
> Report003 =A0 =A0Query093
> Report004 =A0 =A0Query938

   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 =3D Reports(obj.Name)
       Print #1, rpt.Name, rpt.Properties(0)
       DoCmd.Close acReport, rpt.Name, acSaveNo
   Next

   Close
   Set rpt =3D Nothing
   Set obj =3D Nothing

   End Sub

Regards
Marco P
0
Marco
5/15/2010 2:12:14 AM
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
> .
> 
0
Utf
5/17/2010 9:41:01 PM
Reply:

Similar Artilces:

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Advanced Financial Reports--Correct Type & Source
My Advanced Financial Reports have been modified and are incorrect. My Balance Sheet doesn't balance. It is subtracting Net Profit instead of adding it. Could someone please look at their Advanced Financial Report Definition Screen and tell me what the Type and Net Income/Loss Source is to be for Balance Sheet and Income Statement? I greatly appreciate the help. For my Balance Sheet, the Type is "Balance Sheet" and the Net Income/Loss Source is "P&L 2005", which is the name of my primary income statement. For my Income Statement, which is "P&...

Using CImage?
I am trying to use CImage for the first time. I give the code schematically below. CImage::Save() is not working for me. Am I missing some important step? The HRESULT indicates an unknown error. (Stepping through image.Save(), I see it succeeding until the last step Gdiplus::Bitmap bm( m_hBitmap, NULL ); status = bm.Save( pwszFileName, &clsidEncoder, NULL ); which fails.) ================ The code: Schematically I have HBITMAP hBitmap = ......; // I then call a debugging routine which displays the bitmap // in a dialogue: it looks ok. // I now want to save it as a GIF T...

using parameters
I have a form which the user selects the BlockNo. The other information that is entered in the form is : 1) NoOfRecordedTrees - RT 2)NoOfSurroundingTrees - ST When the BlockNo is entered, a query runs which picks up fertiliser rates for this Block for all sectors within that block. With RT and ST - it should do a calculation such that it uses Rate/ sector * (RT+ST) to find how much fertiliser is needed for each sector in each block. I would like to add a column to the existing query showing FertiliserAmt using these parameters. How do I specify them within the query. Thanks for your great...

Crystal Reports Retrieval
How can I retrieve reports that were already created in crystal reports into RMS? Copy the .def and .rpt files into the C:\Program Files\Microsoft Retail...\Store Operations\Crystal Reports folder. Make sure that they are written in CR 8.5 as no other version will work. If you are asking about a memorized report, SO Manager | Utilities | Memorized Crystal Reports -- * "Frank" <Frank@discussions.microsoft.com> wrote in message news:4567BBB9-6173-4D18-9538-D4680AEA91D5@microsoft.com... How can I retrieve reports that were already created in crystal reports into RMS? ...

Beginner
Hi ! I am a beginner to databeses, and particulary to MS Access 2003. (althought I passed the first lessons) I want to make a small database with possibility to grow up. (max. 100 entries for a table) I think that in my case the data must be structured like loop into loop. Like that: Year: 2000 2001 2002 2003 etc. Plant: plant_1 plant_2 plant_3 etc. Generator: gen_1 gen_2 gen_3 etc. So for every Year it must exist some Power Plants, and for every Power Plant it must exist some Generators. I already have ma...

Need the ability to Moderate distribution lists in Exchange
We are moving off of a mail list server and pulling all mail lists into Exchange 2003. Currently users have the ability to moderate mail sent to some lists. (Mail is sent to the moderator first, they approve or deny it, and based on approval it's sent to the list.) How can we set this up in Exchange 2003 SP1?? Thanks! On Wed, 29 Jun 2005 10:38:03 -0700, Lee <leemack33@hotmail.com> wrote: >We are moving off of a mail list server and pulling all mail lists into >Exchange 2003. Currently users have the ability to moderate mail sent to >some lists. (Mail is sent to...

Format Date in Report
I have a report (Certificate) which I would like the date to convert from say 2.2.10 to 2nd February 2010. The Field on the Report is [Date of Request] Could someone kindly reply with the correct syntax to include the formatting in this text Box? Also, if say the date is 3.2.10. can this be converted to 3rd February 2010, ie can you tell Access to record 2nd, 3rd etc? Many thanks for any help Access doesn't have the ability to put the ordinal suffixes onto numbers. You'll have to write your own function to do that, something like: Function FormatOrdinalDate(InputV...

SO detail sales not equal X report
I have a very strange situation. At a closing 3 Blind-Close-out (1 day = 3 shifts), we printed a detail report for a specific date and noticed that the total sales does not equal a blind close-out X-Report all 3 reports. The details is $18,752.00 and the sum of teh X-Report displays $9.525.11 At this point we are comparing actual receipt sales to see what happened. My question is, whats the recommended way to analyze this type of situation and where shoudl we be looking besides actual paper receipt? There may have been some sales transactions AFTER the X report was printed, ...

Report Writer Text Spacing Problems While Modifying a PO form
Due to Report Writer static text field size limitations, I am forced to try to use two adjacent text fields to complete a sentence. I am having an inordinate amout of problems trying to get words to align in sentences with the correct amount of character spacing between words that span the text field boundaries. And, the process of toggling between Report Writer and Dyamics to get the Purchase Order form to reprint is time consuming. What am I missing (besides patience)? -- Jay Jay, 1) Setup a calculated field for your static text. Set the result type to String, and use the Constant...

How to use only half DIN A 4
Tengo que confeccionar nuevas listas de precios para mi negocio, las cuales van introducidas dentro de un soporte de plástico. La medida de las listas es de la mitad de din A4. Tengo hecha la plantilla en din A4 mediante Publisher y no se como reducirlo a la mitad. Puede alguien ayudarme por favor? Trabajar con texto http://office.microsoft.com/es-hn/assistance/CH062524573082.aspx -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "Kat...

Designing Report Troubles
Hi, We just tried to customize a report with Visual Studio 2003 We followed the instructions from the book Working with Dynamics CRM 3.0 from MS Press. We downloaded the reports, we set up a project,.. ... not made any changes yet .. ... but the report is not able to preview and brings up the following error "No connection to Adventure_works_cycle" even we made a connection to sa .. and the connection was tested successfully. Why is the report drying to use a database that is not installed? (I know what the adeventure works cycle database is :-)) Why is AWC named in the "...

picture manager uses incorrect printer as default
Picture does not use the default printer but defaults to the fax. ...

Copying recordset multiple times using copyfromrecordset
We recently caught this issue but found out after research, that this has been occuring all year. This code worked in the past, but I am not sure if there was an Excel update that occurred which changed the behavior of the script. What we are trying to do is to have a recordset pasted twice into two seperate worksheets. It is pasting without issue in the first copyfromrecordset, but then it skips over the second paste (even when stepping through the code). I know that it is not the worksheet we are copying into because I can reverse the 'first paste/second paste' (see lab...

Cannot send mail using either port 25 or 587
I don't understand why this has only recently been happening but I cannot send mail using smtp in ol 2003. I am trying to send using my Yahoo account but the send receive says that it is sending messages that are not actually in the outbox, e.g (bottom rh corner of screen sending 2 of 3 messages when there are no messages there to send) then after a short while it says error send receive. My ISP is BT and apparently they block port 25 so I changed the port settings (as recommended by Yahoo) to port 587 but I still cannot send from within outlook. I have configured my e mail accou...

Workers Comp Summary report blank
When we print the Workers Comp Summary report for any Period or any Month, all we get are zeroes. It doesn't even print report or column headers. I had worked in previous months because we have the postings to prove it. We do have workers comp code in the payroll transaction table. I tried recreating the reports and forms dictionaries. I ran CheckLinks. The report works fine in other companies. Any ideas? If you print the report to screen and then select the 'Modify' button at the top of the report, what does it pull up? This should launch Report Writer s...

trouble connecting to Exchange 2003 using Outlook 97 Exchange Server Service
hello, this is my setup I have one Active Directory domain containing two AD Sites, configured through AD Sites and Services, Site one is the local LAN on the 192.168.x.x subnet, Site two is a remote site in a datacentre on the public-facing 87.127.x.x subnet - the two sites are connecting via VPN on Sonicwall firewalls the VPN connection is solid - and commuications from Site one to Site two is fine, and vice versa netdiag and dcdiag from both sides report no problems My Exchange Server is in Site two, along with a DC/GC and appears to be working fine (still in testing phase), OWA works...

Layaway Report #2
Does anyone out there have a layaway report that has a column for the balance remaining on the layaway? My accountant keeps bugging me for this report; I gave him the Layaway Payment report in CustomerSource, but he really needs a balance column added on to that. Can anyone help me out? Thanks. This is a multi-part message in MIME format. ------=_NextPart_000_0613_01C743D5.2A88EC60 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0614_01C743D5.2A88EC60" ------=_NextPart_001_0614_01C743D5.2A88EC60 Content-Type: text/plain; charset="utf-8" Content-T...

RMS HQ OLAP Reporting
We are looking for someone to write some HQ OLAP reports that we can open in pivot tables in Excel. We have some fairly specific requirements but I'm guessing that someone has already written the type of reports we are looking for. I also noticed on partnersource that there is an RMS HQ OLAP SDK. Has anyone used this to extend the reporting capabilities? Hi Greg, I have used the OLAP SDK a few times. If you are in the cube build from that SDK is pretty good. If you are outside you need to go back through the time dimensions etc.. What did you have in mind? Ivan Brebner &q...

Additional fields in the Activity Table / Case Timer
I have a client who needs accurate billing from the the Activities performed towards the resolution of a case. The ideal solution would be a timer on the Activity Form. However, this is not possible in v1.2 However, in the ActivityBase table there are fields called ActualStart, ActualEnd and ActualDuration. I am planning on writing a report which uses these fields. I would like to know how the application uses these fields, as it would seem to be counterintuitive. I say counterintutitive because when I created a calculated field and subtracted the ActualStart from the Actualend I ...

Uncheck "always use the selected program to open this kind of file" by default
http://www.vistax64.com/newreply.php?do=newreply&p=1136177 Andrew;1136177 Wrote: > If I right click on a file there is usually the option to"Open with..". > Selecting this option list possible programs with which to open the > application and, below this, there is a check box labelled "always use > the > selected program to open this kind of file". The check box is always > checked, so clicking on OK permanently associates the selected program > with > the file type in question. > > However, I usually use this option to op...

Sender listed as <>
When sending emails to distribution lists, in Exchange que, sender is displayed as <>. Why does it show this instead of the person sending the email? I think this is causing some of my emails to not get through to certain domains. Any suggestions? In news:CC65CEBA-759C-4FF4-9348-384942529DAF@microsoft.com, Leni1 <Leni1@discussions.microsoft.com> typed: > When sending emails to distribution lists, in Exchange que, sender is > displayed as <>. Why does it show this instead of the person sending > the email? > > I think this is causing some of my emails to no...

Modified Reports security #2
Hello everyone, I was curious of anyone else out there has recognized issues with Dynamics GP 10.0 and Windows Server 2008 recognizing the forward slash (/) to the shared modified forms and reports dictionaries in the Dynamics.set file. We have had issues with not being able to open Report writer unless we change the forward salashes to back slashes. We have also found that one of our Third Party modules' modified reports shows up in customization maintenance but does not show the option to select the modified window in Security. Has anyone else recognized similar symptoms? Thanks...

Change Headers That Exchange Uses To Deliver Mail?
When someone sends a BCC mail to recipients in my domain, the TO: header field is blank and as a result, the message is getting dumped into my badmail and a copy is going to the postmaster in my domain. It turns out that our ISP has some kind of SPAM filter that parses the BCC content prior to delivery to me and places the intended recipients in a new header called: X-NETSCAN-TO: Is there a way to tell my exchange server to use the contents of that new header to determine the recipients in addition to the normal TO: header which works the rest of the time? So, have it check TO: and X...

IMF white list
Hi Is possible to create a white list in IMF? On Tue, 8 Jun 2004 12:56:07 -0700, renato yukio kawamura wrote: > Hi, > Is possible to create a white list in IMF? No not in IMF, but it's possible to create a whitelist using the Exchange 2003 native filtering options (Message Delivery > Sender Filtering > Accept) or by using the Outlook/OWA 2003 Junk E-mail filter. -- Regards Henrik Walther Exchange MVP Exchange-faq.dk ----- Henrik Walther [MVP] wrote: ---- On Tue, 8 Jun 2004 12:56:07 -0700, renato yukio kawamura wrote > Hi > Is...