Multiple Recordset from a Stored Procedure

[Scott]

Front-end Access 2000

I have a stored procedure that has 2 parameters BusinessUnitID and
Year.
It returns multiple record sets (5 to be exact).
I thought I could use a Pass through query but that only returns the
first record set.
I thought I could use ADO but that does not seem to work. I get an
Error

When I change the SQL from SQL = "procIPCRep ort " & i & ", " &
txtYear.Value
To
SQL = "select * FROM tblBudget

It opens and works

The reason I cannot use multiple calls to the database is the last 4
record sets are generated from the information in the first record set.
The procedure can take 5 minutes to generate results so going back to
the server is not an option.

So my question is I need either 5 different record sets or 1 record set
containing multiple record sets

How should I accomplish this?

[Terry Kreft]

So you'll pop back in about 20 years and let us know how your research went
then ?

<BG>

--
Terry Kreft

"Randy Harris" <ra***@SpamFree .com> wrote in message
news:Gs******** **********@news svr14.news.prod igy.com...

"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:eu******** ************@ka roo.co.uk...

I'm sorry but I don't.

I wouild have thought a search on something like "Oracle Multiple
Recordsets" would return some results.
--
Terry Kreft

Thanks very much Terry. I searched on that very phrase (43,000 hits).

Randy Harris

[Lyle Fairfield]

I know zip about Oracle and zip + 1 / infinity about multiple
recordsets but I am so interested that I couldn't resist the search
myself:

I came upon:
http://www.oracle.com/technology/sam...db8/index.html
Returning multiple recordsets from a stored procedure[13-Mar-2003]
This sample shows how to return multiple recordsets from database
stored procedure using ActiveX Data Objects (ADO) with VB
Download Now (ZIP, 103KB)

Also I note that ADO help suggests that we can roll our own multiple
recordset returning command as:
"If you open a Recordset object based on a compound command statement
(for example, "SELECT * FROM table1;SELECT * FROM table2") using the
Execute method on a Command or the Open method on a Recordset, ADO
executes only the first command and returns the results to recordset.
To access the results of subsequent commands in the statement, call the
NextRecordset method."

And I experimented with MS-SQL as follows:

Dim r As ADODB.Recordset
Dim r2 As ADODB.Recordset
Set r = CurrentProject. Connection.Exec ute( _
"SET NOCOUNT ON " _
& "SELECT * FROM FFDBATransactio ns " _
& "COMPUTE AVG(TotalAmount )")
Set r2 = r.NextRecordset
Debug.Print r.GetString(adC lipString, 1, vbTab, vbNewLine)
Debug.Print r2.GetString(ad ClipString, 1, vbTab, vbNewLine)
Set r = Nothing
which gives:

1 2004-01-01 Carryover from 2003 7290.07 17 1 11

591.5508

And I'm going to ask a question about multiple recordsets and no count
in a separate thread.

[Randy Harris]

"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:_b******** ************@ka roo.co.uk...

So you'll pop back in about 20 years and let us know how your research went then ?

<BG>

--
Terry Kreft

I downloaded some sample code from Oracle. So far I haven't gotten it
working (OLEDB driver issue), but I hope to. I'll report back as soon as
I've got something working.

Randy