Solved

View Report Source Code ??

Posted on 2002-04-06
15
257 Views
Last Modified: 2008-02-26
Hi Gurus

I wam wanting to write a application that opens a access database and searches through all the reports.

for each report i want the application to identify what each one does..

i.e. what tables it uses, sort bys, group bys e.t.c.

However when you open a report, you cannot see any source code.

I thought my only chance was to parse the syntax and use a string compare function to identify keywords and then produce a word document for each report that the owners of the database then can use to identify what each of their reports do.

The reason being...my customer has 150 reports and cannot remember what each one does.

any ideas???
0
Comment
Question by:onegui
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 9

Expert Comment

by:Volibrawl
ID: 6922627
How about Tools/Analyze database documentor?
0
 
LVL 9

Expert Comment

by:Volibrawl
ID: 6922629
hmmm I guess that does not give you much of what you need.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6922939
I've never used report information to show users what they are used for.
Normally a user defines what he needs to see and I build the report.
To build a report I start with making reportlayouts with data so the user can see what's on the report. That's telling him much more as just the table and the sort/grouping on fieldnames he doesn't recognize....

Best action will be to create a print of each report and show that to the user.

Nic;o)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:onegui
ID: 6923132
HI Nic.

thanks for your advice...
The report information was actually going to be used for other programmers/analysts to look at and since there are 150 plus reports, then this was going to be a useful time saving tool, but it looks like it is impossible to create an application that does this??

cheers onegui
0
 

Author Comment

by:onegui
ID: 6923135
HI Nic.

thanks for your advice...
The report information was actually going to be used for other programmers/analysts to look at and since there are 150 plus reports, then this was going to be a useful time saving tool, but it looks like it is impossible to create an application that does this??

cheers onegui
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6923158
You can extract the info you need by accessing the Access container object from VBA code. Just checkout the helpfile for some sample code on containers and the documents stored in them.

But as I stated above, when the fieldnames are "cryptic" to the users(including programmers/analists), this will be of little use.

I normally design applications based on reports the user shows me, so if you want a redesign of the system, I would start with the print of reports with data.
Next try to group them, as your 150 reports will certainly have features in common like orderdata per day/week/month/year or by producttype etc.

Having categorized the reports, I would assess the fields and recreate from them the database structure needed for the application. This is known as the data-driven design approach. Then you're able to judge or the present table design is appropriate.

Nic;o)
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6923368
Just adding my 2 cents.

150 reports, wow !! that's a lot.  any way that you could code some logic that would modify existing reports so you don't have to create so many different reports.

you can control grouping, sorting, and new controls all through code and you could use one report to produce 30-40 different outcomes.

I've created logic and used these methods to use one report that produces hundreds of different combinations of outcomes for reporting.  this way I only have one report object but I can produce hundreds of different outputs.

look up createreportcontrol   and you'll see what I mean.

I know this may not address your specific question but you may want to consider it due to the limitation of objects available in Access.

0
 

Author Comment

by:onegui
ID: 6923477
thanks SE and NIC5038 for your replies....

SO NIC5038..if i understand yuo correctly..

Youre saying that i can access the source code that produces each report, but because of its cryptic nature i still wont be able to identify what fields are being used???

I dont think i need to produce a detailed report on what fields are produced as such, but a general overview of the main table accessed, and what type of sortby and groupbys are used??

does this sound any better??

onegui
0
 
LVL 2

Accepted Solution

by:
kiddiec earned 50 total points
ID: 6924201
Try this to see if it will give you what you require

Sub ItterateReportDetails()
    Dim obj As AccessObject, dbs As Object, rpt As report
    Set dbs = Application.CurrentProject
    ' Search for open AccessObject objects in AllReports collection.
    For Each obj In dbs.AllReports
        DoCmd.OpenReport obj.Name, acViewDesign
        If obj.IsLoaded = True Then
            ' Print name of obj.
        MsgBox Reports(0).Name
        MsgBox Reports(0).RecordSource
        DoCmd.Close acReport, obj.Name, acSaveNo
        End If
    Next obj
End Sub

Of course the message boxes will need to be changed to debug.print, or even to pront to a file or save the data in a new table
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6924228
Good code kiddiec, only there is still the problem of reports based on a query that involves more then one table and for sorting you'll need to access the "ControlSource" property of the "GroupLevel"(s) that is/are possibly present...

Nic;o)
0
 
LVL 9

Expert Comment

by:ornicar
ID: 6924380
Nico forgott to mention the eventual sub-reports. You have to browse the controls to check for subreports.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6924576
Good point ornicar !
That triggered the thought that sometimes the recordsource is created dynamically from VBA....

Nic;o)
0
 

Author Comment

by:onegui
ID: 6925165
Hi Gurus..

firstly..thanks a bundle for your help!!!

ist question...
The syntax reports(0).recordsource displays the whole select query. so if there is mutiple tables, then they are shown also, so thats fine.

I need to clarify..subreports.
When you create a report that may have mutiple tables and may include many groupings..

i.e. Date..then this creates sub reports correct ??
If then i need to somehow identify what the group by is?

I noticed that reports has a property Orderby though this is not returning any values when used with reports(0).orderby.

Is there anyway of establishing the sortby, groupby and orderbys ??

much appreciated
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6925641
I already stated:
For the sorting you'll need to access the "ControlSource" property of the "GroupLevel"(s) that is/are possibly present...
The queries OrderBy is "suppressed" by the sort of the grouping.

Subreport(s) can be placed on a report linked to the main report and have their own datasource.

You still will miss the reports that have dynamically generated queries and also the above mentioned Grouplevels can be manipulated by code....

Nic;o)
0
 

Author Comment

by:onegui
ID: 6926973
Hi kiddiec

thanks for your syntax

i will now attempt to use your syntax in addition with the control source for identifying any sorts, groups e.t..c

much appreciated

onegui
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question