ADO rst => Access Report

[Carter Lekatz]

Does anyone have any recommendation as to what would be the best method to get an
ADO recordset into an Access report? I see that access doesn't allow

set MyReport.Recordset= rstTemp

Is there another method that I might be able to hook up an Access report to an SQL
table valued function returned from MS SQL 2000?

TIA

 

[SA]

Carter:

Assuming that your table is created in an SP by calling the function, then
have the last part of the SP to a select from that table, returning a row
set. Then set the report's record source to be the row set.

 

[Carter Lekatz]

Steve,

I retrieve recordset in Access by calling a Table valued function from an MS SQL
2000 database. So there is no table in either Access or SQL and I'd like to be able
to use just the recordset instead of having to create the table, but if the only way
is to create a table and set the recordsource to the temp table I will have to do
that. That brings me to my next question what's the best way to create a temp table
in Access that will always be unique... (like in sql you have the #tmp tables that
are local to the sp or function and are only available in that scope) so when one
user clicks the button to open the report at the same or almost same time as another
user there are no conflicts...

The nice thing about the #tmp tables are that they're only around for the scope of
the function. Is there a way to do the same thing in Access?

Thanks again to everyone for the help!

--
Carter

Carter:

Assuming that your table is created in an SP by calling the function, then
have the last part of the SP to a select from that table, returning a row
set. Then set the report's record source to be the row set.

 

[SA]

Carter:

Stop by our web and look in the reports section. We have a whole section on
creating and using unique temp tables in Access connected to SQL server.

On the otherhand, if you can create your recordset as part of an SP, then
you can in Access Xp (not in 2000,) set the recordsource = Exec MySP param,
param etc.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Steve,

I retrieve recordset in Access by calling a Table valued function from an MS SQL
2000 database. So there is no table in either Access or SQL and I'd like to be able
to use just the recordset instead of having to create the table, but if the only way
is to create a table and set the recordsource to the temp table I will have to do
that. That brings me to my next question what's the best way to create a temp table
in Access that will always be unique... (like in sql you have the #tmp tables that
are local to the sp or function and are only available in that scope) so when one
user clicks the button to open the report at the same or almost same time as another
user there are no conflicts...

The nice thing about the #tmp tables are that they're only around for the scope of
the function. Is there a way to do the same thing in Access?

Thanks again to everyone for the help!

 

[Guest]

Thanks much I'll give it a try!

Carter

Carter:

Stop by our web and look in the reports section. We have a whole section on
creating and using unique temp tables in Access connected to SQL server.

On the otherhand, if you can create your recordset as part of an SP, then
you can in Access Xp (not in 2000,) set the recordsource = Exec MySP param,
param etc.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

an
MS SQL like
to be able a
temp table so
when one time
as another the
scope of