Recordsource for Access Reports using SQL Server 2000

[Mike Milsted]

Am developing an Access 2000 (mdb) program using linked
SQL Server 2000 tables as the data source. In this program
I want to produce a report that has its recordsource as
the result of a stored procedure. When I run the stored
procedure and try to load the results into the report, I
get an error message that says that I cannot load the data
into the report objects (which consist of nothing more
than a set of text boxes).
What are my alternatives? What other way is there to use a
stored procedure to populate a report? Using a make-table
stored procedure to populate a temp table and then load
the report is NOT one of my available options.
I can use ADO connectivity to develop the recordset ant
then load the report, however, the only way this method
works is when I create a form to match the report and then
load the recordset into the form.

Thanks, Mike Milsted

 

[SA]

Mike:

With Access 2000, you can't set the record source to a record set result.

With linked SQL tables, why not create a saved pass through query to your
stored procedure and use that as your record source for the report. As an
example the following pass through query would run the Sales by year SP in
the northwind db and return a recordset.

[Sales by Year] @Beginning_Date = '7/1/96', @Ending_Date ='12/31/96'

You can simply change the SQL prior to running your report by accessing the
querydef in the db and setting the params as desired.