Report from recordset?

  • Thread starter Thread starter andreak
  • Start date Start date
A

andreak

Greetings-
I'm pretty new to Access and I'm taking over some reports
that are currently set up in a different system. I need
to change the query logic from the application that's
being retired to use the output of some more sophisticated
sprocs that are already in use in other areas of the
company.

I've written a test VB app that opens Access and calls the
old Access macro and runs the existing report using the
old pass-through query, so I've got the logistics of
calling Access from VB figured out.

My question is this: Can I somehow use my ending recordset
results as input data for the report? Or do I need to use
a pass-through query and figure out a way to get the
proper criteria into this multi-sproc existing process?

Thanks in advance,
Andrea
 
Andrea:

Good question, probably a long answer.

1.) If you Access Database is an mdb style, then you've go to use a pass
through query. Pass throughs can support sprocs that would return a row
set, including sending them parameters quite nicely. From the northwind
db, you can use the sales by year sproc in a pass through like this in
Access [Sales by Year] @Beginning_Date = '7/1/96', @Ending_Date ='12/31/96'.
To set the parameters into the pass through simply create a querydef and
change its SQL on the fly before your report runs to set in the params. You
can have the report's recordsource set to the pass through query.

2.) If you have Access Xp or greater you can create what's called an ADP
database (which has a direct connection to the SQL server) and run your
sproc and pass parameters in the On open event of the report. You can do
this by running code like this:

Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '1/1/97','12/31/98'"
Me.RecordSource = strRecordSource
End Sub

To get the strRecordSource into the report, you could simply create a module
with a mod level variable and a Function to set the exec string from VB
(using Application.Run) and a function that the report could call to pull
the value when it opens.

This method doesn't work with Access 2000 (even though it supports ADPs) so
you best be there is to work with a passthrough.

HTH
 
Back
Top