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?

[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("Whatev er")

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

Set r(1) = CurrentProject. Execute("Whatev er 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.Exec ute(procMultipl eRst)

doWhatever1 Rst

SET Rst = Rst.NextRecords et
doWhatever2 Rst

SET Rst = Rst.NextRecords et
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.NextRecordse t
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.goo glegroups.com.. .

I see; sounds like a great capability.

[Thelma Lubkin]

Terry Kreft <te*********@mp s.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*********@mp s.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*********@mp s.co.uk> wrote in message
news:bf******** ************@ka roo.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******** ********@newssv r11.news.prodig y.com...

"Terry Kreft" <te*********@mp s.co.uk> wrote in message
news:bf******** ************@ka roo.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*********@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