473,883 Members | 1,665 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple Recordset from a Stored Procedure

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?

Dec 7 '05 #1
12 17680
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

Dec 7 '05 #2
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 ***
Dec 8 '05 #3
I see; sounds like a great capability.

Dec 8 '05 #4
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.

Dec 8 '05 #5
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
Dec 8 '05 #6
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

Dec 8 '05 #7

"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

Dec 8 '05 #8
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

Dec 8 '05 #9

"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

Dec 8 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1514
by: sbw | last post by:
Hi. I have an app which uses multiple databases and I would like to know if there is a way to connect to different databases through OLEdb (or any other connection string) which allows me to connect to a different database. My connection string connects to HelpDesk db. For example
5
1727
by: Eugene Anthony | last post by:
I have created the following stored procedure whereby it will check whether the categoryID is valid and if it is then the updation will be performed, else a -1 will be returned. This procedure will be execute with asp. Is this considered to be efficient?. create procedure usp_updateCategories @CategoryID int @CategoryDescription varchar(30), @ParentCategoryID int
2
11711
by: Josh Strickland | last post by:
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. tCetecM1CUST (SQL Table that contains the Customer Information) tAccountingDetail (SQL Table that contains the information in the form) frmAccountingEntry (Access form used to enter data) spGetCustomerInformation (Stored Procedure which returns data using...
3
5823
by: Suresh | last post by:
Hi All I am writing Import\Export routine in DB2 using stored procedure and front end as JAVA. For this I have two option 1) Writing five different stored procedure returning one cursor (recordset each) 2) Writing one stored procedure returning five different cursor (five
0
1209
by: Hades5k | last post by:
Hello everyone, I'm working a project using MS Project 2003 database. What I need to do is to get all entries for a specific user, from a specific project with specific citerias. Its a web application, made with C#. As it is right now, we're doing multiple queries with Data readers and DataTable, filtering those tables to fill new tables to achieve our goal. But the process takes about 13 sec to complete which is way too long. So I...
1
11333
by: stjulian | last post by:
I have a stored procedure which returns 2 tables and 1 output value. I want the first table to be assigned to rs1 and the second to rs2. However when I run this, I get the following error as I begin to refer to the rs2 recordset (Do while not rs2.eof). I have even used 2 ".execute" statements in the code below, but the code assigns the first recordset twice. Microsoft VBScript runtime error '800a01a8' Object required: 'rs2' ...
6
6655
by: binky | last post by:
Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question: I have a stored procedure that returns a set of records from a SQL Server and loads it into a form in my Access application. This works as intended, but I'm having trouble trying to figure out how to modify records on the form... whenever I try i get the error that says youc an't modify this data because its based on an expression. i'm...
3
10046
by: Dooza | last post by:
Hi there, I have a Stored Procedure in SQL 2000 that takes 10 input paremeters, and outputs 11 recordsets, not rows, but 11 different SELECT statements. The purpose of this is to for a menu system in a store. If all the inputs are NULL, then just the top level categories are output in the first recordset. The ID of each category will then be passed back into the stored procedure where it then filters the second recordset to create the...
6
3491
by: SethM | last post by:
I have a stored procedure that returns a record set. I want to functionalize this so I can have multiple presentations of the same record set. However, I can not get rs_event.open StoreProc to pass through the function, so I can use rs_event("Title"), etc, etc. Is this possible to do? If so how? Thanks. Regards, Seth
0
9778
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11112
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7959
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5784
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4602
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4205
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.