Solved

View Report Source Code ??

Posted on 2002-04-06
15
256 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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 …

856 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