Solved

View Report Source Code ??

Posted on 2002-04-06
15
254 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
 

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
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retrieve Windows Office Files from Parallels 12 VM 9 60
Login screen Access Database 8 29
Run Time Error 3075 15 43
Library not Registered 16 42
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now