Reports bound to ADO recordsets - rant and question

[Nick Stansbury]

Hi,
I am totally stuck with a problem over reports bound to ado recordsets.
The problem centers around this paragraph in "Microsoft Access 2002 -
Using ADO in Microsoft Access 2002", where it says



"NOTE Even though it is possible to set the Recordset property of a
subreport, Microsoft does not recommend that you do so. Microsoft Access
ignores the LinkChildFields and LinkMasterFields properties when setting the
Recordset property. For natively bound subreports, Microsoft Access uses
these properties along with others (RecordSource, Filter, and so on) to
build the recordset for the subreport. Because you are supplying the
subreport's recordset by setting its Recordset property, Microsoft Access
ignores any properties that it would normally use to build the subreport's
recordset. Because you can only set the Recordset property during the
subreport's Open event, it is not possible to create linked subreports using
the Recordset property"



Such a litte innocuous note - who'd have thought it would cause *this much*
trouble? Here is the thing - I've build almost all of an .adp front end to a
dual front-ended sql-sever based system. Following standard practices we've
decided that we're only going to allow client-front ends to access stored
procedures (so direct table access is out). We've also decided that all of
our forms are going to be unbound (also best practice) and to use separate
stored procedures for getting entities, adding entites and updating entites.
Exactly as you're "supposed" to use .ADP's surely? It all works beautifully
with Forms.



However when I started building the various reporting functions that we were
going to need I started running up against a lot of problems.



What you naturally want to do if you've done all of this work using unbound
forms is, from a list form (i.e. a form with say 25 companies on it) I want
to be able to grab that forms recordset, and pass it as the recordset to a
"customer status" report - so taking a recordset with company number (Key),
name, address etc. and then pulling up in various nested sub-reports all of
the details (their open orders, their payment status etc.). Now the most
logical way of doing this is surely to take the bound recordset, pull out
the company no and call ONE SINGLE stored procedure returning multiple
results sets and binding each of my sub-reports one at a time to each of
those results sets - it seems really simple! But you JUST CAN'T DO IT! I
can't seem to find any way of making this work.

You can only bind a report to a recordset in the Open event - so the
solution I tried here was to pass a reference to the recordset via. the
openargs of the report (to a collection of recordsets in a global module)
and bind the recordset in report_open. This works ok - but its a major
clutch. The real problems comes with nested sub-reports - I used exactly the
same technique here which works fine for the first record - but NONE of the
events that a sub-report raises are raised when the parent report pages - so
the sub report seems TOTALLY OBLIVIOUS to changes in the parent! Further
more- just to frustrate me I can't seem to make the sub-report object
"re-load" when the parent report pages either - it gives me a similar bloody
error then too.



What do I do? How on earth can I work around this problem? I know I can
use reports bound to sub-reports using bound stored procedures in some way -
but the documentation isn't great - and it seems so *wrong* to do it this
way - fire 7 or 8 calls to the sql server each time I page the report - for
each sub-report? It's just not right! It just makes sense in every way to
run one procedure, return all of the data you need and pass each sub-report
exactly the data it needs. Am I being blind - is there any way to work
around this?



I'd really appreciate any help



Thanks



Nick

 

[Sylvain Lafontaine]

Use a temporary table (or a permanent table with temporary data). If
necessary, create one or more temporary tables for sub-reports.

 

[Nick Stansbury]

Dear Sylvain,

Thanks for the answer - I'll investigate this workaround. It still seems
like a total cop-out - and if we have to do it this way it means a huge
doubling up of all of our procedures - whenever we want a report to be used
on both the web-client and the access client we're going to have to create
two procedures rather than the one I'd hoped for. Thanks for your help
though. Why have MS done it this way? It just doesn't make any sense to me!

Nick

 

[aaron.kempf]

Microsoft is too drunk with Excel and Windows to ever take Access
seriously.

-aaron

 

[Nick Stansbury]

Maybe - but it doesn't make it any less frustrating - it remains an
excellent tool for doing exactly this sort of project - if it was less
functional and effecient I think these bugs would be less irratating - but
because the rest of the system is so simple it makes the bugs and the stupid
limitations so much more irratating

 

[Malcolm Cook]

I notice that a subreport generates a Report_Open once (oh, well, more than once) for each master report.

So, you might consider building a dictionary of all your many recordsets indexed by the sub-table's primary key and doing the lookup
in the subreports Report_Open.

I've never tried this. I use bound forms and reports in ADPs quite satisfactorily.

But, perhaps I am not addresing your problem. In fact, I'm not sure what 'paging' has to do with your problem at all.

Are you really pursing this approach in your web reporting envrineomtn with a sp returning multiple recordsets, one for each master
record? Wow!

I'm still lurking....

 

[Nick Stansbury]

Dear Malcolm,

Thanks - I'm not sure I reall followed the answer - but maybe I did.
Report_Open only runs once - i.e. when the report first opens - the point is
that when I page through the report report_open doesn't run everytime I page
through the report - so whilst I can use report_open to "Pull" down the
recordsets form each of the parent report items (which in itself isn't
ideal - it means each sub-report could only be used within one parent
report) the recordset is static - and so refreshing the recordset by
reference doesn't seem to help. The idea of a dictionary of linked
recordsets is possible - but what I can't figure out is why, given that I've
never cloned the recordset all references to it should point to the same
instance of the object - so if I create one "payments" recordset there is in
vb no way that I know of to modify that object leaving the other references
is to it pointing to the same instance (if that makes sense - I'm not being
terribly clear). The point is that even if I use the same recordset and just
repopulate it with data I can't get the data inside each sub-report to
change when I page through

Are you really pursing this approach in your web reporting envrineomtn

with a sp returning multiple recordsets, one for each master

record? Wow!

No - misunderstanding - I have one procedure that returns multiple results
set for one master. So for one company it returns multiple payments, order
telephone numbers etc. Inside is a set of views / udf's that are also used
in the procedure that creates a list of any of these entities. The reason is
that the website reporting modules are only required to produce *one* of
these reports at a time. Given that to do it the other way in access would
require either multiple database calls or extensive client side filtering /
grouping - both of which are inneffecient. This way we make one call per
record and do no filtering on the client side at all. It seemed much more
effecient to me...