Should I upsize to ADP?

[winshent]

Hi Guys

I need some advice on this. I've not really done much work with adp's and sql server for a few years now, but my latest project that i've been asked to sort out pulls from a sql server based database.

The reporting database had over 750 queries and 180 tables.. it was a bit of a mess, with lots of duplication and obsolete items..

I've converted all the vba logic/ queries into stored procs/ t-sql functions. I have code to pull sprocs using ADO. With the idea being that it would be easy to migrate reporting to a web app further down the line. I now need to bind the access reports to stored procs..

Is it best to convert to ADP so i can bind the recordset of a report at runtime (in Report_Open event)
or
create a fixed reporting table in sql server that the report can bind to, and so stick with MDB format?

The latter would require clearing out and reloading of the table each time before opening the report.

Whats the best solution..

 

[winshent]

when dynamically binding the recordset of a report in the report_open event. should the recordset property in Design view be set to the stored proc that is being called?

 

[LPurvis]

Dynamically binding would involve assigning the recordset to the report.
Set Me.Recordset = rstObject

This functionality, as you point out, is only available in ADPs.
(I've asked MS about this and it hasn't happened in MDBs yet - and don't hold your breath would be my opinion).

The choice of report binding would be yours (i.e. ADP or MDB).
It depends upon your needs. This Server reporting table you had in mind - what would the scope of it be? Visible to (and used by) all would be extremely problematic of course. Hence in MDBs passthroughs and local tables (for subreports) are often used. If you don't have much requirement for subreports then that's even better. (One day linked subreports will support passthroughs - it's a bit of a shocker that they still don't).

Cheers.

 

[winshent]

Hi Leigh

Thanks for responding.

I've taken some of the reports across to a new ADP project. I was initially binding the report at runtime using set me.recordset = rs...

This all works fine EXCEPT for reports that have grouping. I've only tried this with one report which had grouping and it totally crashes Access.. So i tested by designing a new report and adding one text control, and grouping on that field.. it still falls over..

I have since tried the method described in this KB article.

http://support.microsoft.com/kb/300693

This isnt really what i wanted to do as it means having to hardcode parameters into the design of the report.. but i cant even get it to work !

I have bound the report to the sproc (and removed binding to recordset at runtime).. and have specified my Input Parameters as:

@RouteFrom char(1) = Forms!frmLabelTrans!txtRouteFrom, @RouteTo char(1) = Forms!frmLabelTrans!txtRouteTo, @DayNo int = Forms!frmLabelTrans!txtDayNo, @WeekNo int = Forms!frmLabelTrans!txtWeekNo, @YearNo int = 2009

So, in summary i cant get either to work.. any ideas on how to fix both would be appreciated..

 

[LPurvis]

I've not used report binding other than in brief playing (I very rarely use ADPs in general anyway). Grouping seems like it might be a deal breaker (it doesn't crash for me... but it does fail to group and reports an error).
Hmm - that's actually pretty useless. Without grouping you can achieve just the same with an unbound report (and you can use those in an MDB too)!! :-s

As for the built in SP parameters method... (It's good that this functionality is offered - though I admit for forms I prefer recordset binding for control).
Surely your parameters should be just:

@RouteFrom = Forms!frmLabelTrans!txtRouteFrom, @RouteTo = Forms!frmLabelTrans!txtRouteTo, @DayNo = Forms!frmLabelTrans!txtDayNo, @WeekNo = Forms!frmLabelTrans!txtWeekNo, @YearNo = "2009"

And even then the names, if supplied in order, can be optional as I recall.

Forms!frmLabelTrans!txtRouteFrom, Forms!frmLabelTrans!txtRouteTo, Forms!frmLabelTrans!txtDayNo, Forms!frmLabelTrans!txtWeekNo, "2009"

Cheers.