Repost - Recordsource For Reports

[Randy]

Hello NG -

Newbie ? Somewhat.

Access 2003 on a windows 200 OS - connecting to a windows server 2003 OS
with a SQL Server 2000 DB. I am making a connection to the server and
returning from a stored procedure a recordset. I want this recordset to be
the recordsource of a report - My thought process is I either have to turn
my mdb into an adp,which I am hesitant to do because of local tables, or
move the recordset to a temporary table within access,which is doing double
the work
for the singular task, and then have the report draw
its recordsource from this table.
Bottom line Can an ADO recordset be used for a report recordsource?

TIAFAH

Randy

 

[Jeff Boyce]

Randy

Can you make a query that returns the recordset? If so, base your report on
the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Randy]

I have dimmed both a connection and a recordset in the report open module
I have opened the connection
and I have EXECX the stored procedure within SQL to return the recordset to
my recordset variable. I just need to make the report use this recordset
variable correct? But when I close the connection I lose the recordset and
when the event procedure ends I also lose the recordset. Am I Not seeing
something?

Randy

 

[Jeff Boyce]

Randy

If I remember correctly, you may not be able to modify the report's source
from within the report (i.e., after you've opened it).

Another approach might be to open the recordset in code behind a form, set a
querydef's source to that recordset, and open the report, already pointed at
the querydef.

Just a thought...
Regards

Jeff Boyce

Microsoft Office/Access MVP

 

[RoyVidar]

Bottom line Can an ADO recordset be used for a report recordsource?

Access reports have a recordset property available starting from either
the 2000 or 2002 version, but as far as I know, it can only be set in
an ADP.

The recordsource property of reports, as far as I know, needs the name
of a table or query, or dynamic sql (sql string).