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 = "procIPCReport " & 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?

[Lyle Fairfield]

You have a SPROC that returns five recordsets?

And ADO does not seem to work?

OK. I confess that I have not seen a SPROC that returns five
recordsets, or even two.

Secondly ADO works. When there's an error it's because someone made an
error.

I'm guessing you need five different recordsets. I'm thinking you get
them by getting the first recordset in ADO, selecting from it the
values needed to define the others and using them to get the other
four.

I'd start with

Dim r(5) as ADODB.Recordset

Set r(0) = CurrentProject.Execute("Whatever")

' get value from r(0); I'm assuming these are parameters?
' use the values to for

Set r(1) = CurrentProject.Execute("Whatever Wherever");

etc

[Scott Remiger]

I had a problem in the SPROC I forgot to SET NOCOUNT ON

In ADO however when return multiple recordsets you loop through the
recordet something like this

SET Rst = Connection.Execute(procMultipleRst)

doWhatever1 Rst

SET Rst = Rst.NextRecordset
doWhatever2 Rst

SET Rst = Rst.NextRecordset
doWhatever3 Rst

etc

Works great when loading a form with many different list only requires
one call to the database
Note: Rst 2 to N are Readonly Forward Only recordsets


*** Sent via Developersdex http://www.developersdex.com ***

[Lyle Fairfield]

I see; sounds like a great capability.

[Terry Kreft]

Lyle,
It's also useful for things such as invoices.

You can make one call and get the header and details back, you access the
details using the Nextrecordset method. This is most useful in unbound mode
I find.

I tend to do something like (in VB not Access)

Dim varHead as Variant
Dim varDets as Variant
DIm rs as adodb.Recordset

' code to open the recordset

varHead = rs.GetRows
set rs = rs.NextRecordset
varDets = rs.GetRows

' do the rest of the work

(That's all air code so don't rely on it)

This way you end up with a couple of arrays holding your data which are then
faster to iterate through to carry out unbound data assignment etc., you end
up with an app which is so fast it's blinding.
--
Terry Kreft

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

I see; sounds like a great capability.

[Thelma Lubkin]

Terry Kreft <te*********@mps.co.uk> wrote:
: Lyle,
: It's also useful for things such as invoices.

: You can make one call and get the header and details back, you access the
: details using the Nextrecordset method. This is most useful in unbound mode
: I find.

: I tend to do something like (in VB not Access)

: Dim varHead as Variant
: Dim varDets as Variant
: DIm rs as adodb.Recordset

: ' code to open the recordset

Can you please explain, not simply show code, the essence of
opening a recordset so that it apparently becomes a set of
recordsets, not a single continuous set of rows? I tried to
read Microsoft's documentation for NextRecordSet, but I'm afraid
that I didn't understand...(I also have never looked at VB without
Access, so this parenthesis just alerts you to my discomfort
with yours)
thanks, --thelma
: Terry Kreft

[Terry Kreft]

Say you have a stored procedure in SQL Server which looks like this

Create Procedure Invoice
@InvNum char(10)
AS
SET NOCOUNT ON
-- Return the invoice header data
SELECT
c.name,
c.address1,
c.address2,
c.address3,
c.address4,
h.*
FROM
inv_head h
INNER JOIN
customers c
ON
h.customer = c.customer
WHERE
h.inv_num = @InvNum

-- Return the invoice details data
SELECT
*
FROM
inv_dets d
WHERE
d.inv_num = @InvNum
SET NOCOUNT OFF

Note the stored procedure returns two recordsets.

If you then open an ado recordset using the above stored procedure the
recordset will hold the invoice header data, using the NextRecordset method
then populates the recordset with the invoice details data.
--
Terry Kreft

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:dn**********@uwm.edu...

Terry Kreft <te*********@mps.co.uk> wrote:
: Lyle,
: It's also useful for things such as invoices.

: You can make one call and get the header and details back, you access
the
: details using the Nextrecordset method. This is most useful in unbound
mode
: I find.

: I tend to do something like (in VB not Access)

: Dim varHead as Variant
: Dim varDets as Variant
: DIm rs as adodb.Recordset

: ' code to open the recordset

Can you please explain, not simply show code, the essence of
opening a recordset so that it apparently becomes a set of
recordsets, not a single continuous set of rows? I tried to
read Microsoft's documentation for NextRecordSet, but I'm afraid
that I didn't understand...(I also have never looked at VB without
Access, so this parenthesis just alerts you to my discomfort
with yours)
thanks, --thelma
: Terry Kreft

[Randy Harris]

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:bf********************@karoo.co.uk...

Say you have a stored procedure in SQL Server which looks like this

I am absolutely intrigued by this technique. Seems to me that it might
provide a substantial performance benefit. Any chance you might know where
I can find an example that uses an SP in Oracle to create multiple
recordsets like that?

Randy Harris

[Terry Kreft]

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

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:e6****************@newssvr11.news.prodigy.com ...

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:bf********************@karoo.co.uk...

Say you have a stored procedure in SQL Server which looks like this

I am absolutely intrigued by this technique. Seems to me that it might
provide a substantial performance benefit. Any chance you might know
where
I can find an example that uses an SP in Oracle to create multiple
recordsets like that?

Randy Harris

[Randy Harris]

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:eu********************@karoo.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

[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******************@newssvr14.news.prodigy.c om...

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:eu********************@karoo.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.Execute( _
"SET NOCOUNT ON " _
& "SELECT * FROM FFDBATransactions " _
& "COMPUTE AVG(TotalAmount)")
Set r2 = r.NextRecordset
Debug.Print r.GetString(adClipString, 1, vbTab, vbNewLine)
Debug.Print r2.GetString(adClipString, 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*********@mps.co.uk> wrote in message
news:_b********************@karoo.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