Solved

Returning multiple recordsets - Excel VBA + ADO

Posted on 2013-10-24
13
1,541 Views
Last Modified: 2013-11-08
Hello Experts,

Please provide your expertise on below requirement.

I want to get 2 recordsets returned from my select query through ADO using excel VBA. I am using excel 2010.
database = oracle 10g
connection string = Provider=OraOLEDB.Oracle;Data Source=PRODSLD;PLSQLRSet=1;User Id=DUMMYUSER;Password=DUMMYPASSWORD
current select query (e.g.):
select distinct assettype from product

excel vba code to get the data
oRecordSet.Open sQuery, oConnection, , , ADODB.CommandTypeEnum.adCmdText

I want to change my current select query to return 2 recordsets instead of one. for e,g.
select distinct assettype from product
select price, code from sales

is this possible and how can I do that?

Thank you very much for your time.

$wapnil
0
Comment
Question by:spattewar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39597268
As long as the provider supports it (I would assume Oracle does), you can just use:
sQuery = "select distinct assettype from product; select price, code from sales"

Open in new window


then when you need to access the second recordset you can use:
Set oRecordSet = oRecordSet.NextRecordset

Open in new window

0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 500 total points
ID: 39597274
I want to change my current select query to return 2 recordsets instead of one

As far as I know, it's not possible.

But you could do a cartesian product if you need both data in one recordset, like
select distinct 'P' tab_type, null price, null code, assettype from product
union
select 'S' tab_type, price, code, null assettype from sales

Open in new window


BUT if rorya's solution works, please let me know ;-)
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39597360
I know mine works with SQL Server but I've never had occasion to use it with Oracle. :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Author Comment

by:spattewar
ID: 39597439
hi Rory, Alex,

thanks for your response. I am trying Rory's solution first and am getting this error

ORA-00911: invalid character

the query that I am using is

SELECT DISTINCT 1 FROM SLPE.DAILY_PORTFOLIO_AVG DY ; SELECT DISTINCT 2 FROM SLPE.DAILY_PORTFOLIO_AVG DY

do I have to set any options? Thanks.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39597661
A quick Google suggests that for Oracle you may not need the semicolon.
0
 
LVL 22

Author Comment

by:spattewar
ID: 39597779
hummm..does not work. says that the sql command not properly ended.

I think one time it gave a pop up saying that the provider does not support multiple recordsets but now I am not getting that error. weird.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598237
A quick Google suggests that for Oracle you may not need the semicolon.

Please provide link. 2 separate select statements without a terminator (semicolon by default) won't work!

I don't think that you're able to do this with Oracle (I'll take a closer look at that). So, you have 2 options: either use 2 recordset objects (hence 2 sep SQLs) or build a cartesian product and use 1 SQL...
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598268
http://www.kamath.com/books/excerpts/be004_rds7.asp

Can I use Multiple Recordsets with Any Database?

Not every database, driver, and provider supports multiple recordsets. I've never been able to get them to work with Oracle. SQL Server works like a champ with them (in some cases – for example, when server-side cursors are used with ODBC, you get an unpleasant error message).
0
 
LVL 22

Author Comment

by:spattewar
ID: 39598387
Yes. Alex. the link says it all. I am also not able to return multiple recordsets.

I have gone ahead and created a query as you have suggested and then am using parts of the resultset to populate the sheets. not a great way but it works !!!

I am just waiting for Rory to provide some information of his search and then I will close this question.

Thank you for your time.
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39598426
not a great way but it works !!!
That is so true :-( Quick & Dirty / Bad practice

I'd rather go for 2 sep. statements and put the results into 1 or more arrays, something like that, I don't know ;-)
In the end, it's up to you and your needs..

Best regards & good luck,
Alex
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39599942
http://bytes.com/topic/access/answers/434019-multiple-recordsets-continued

this appears to indicate that Oracle can do it (client side cursor required), at least using SPs. Whether you can pass a double SELECT statement as Terry Kreft indicates in a post about a third of the way down, I don't know, as I don't think I have access to an Oracle DB here any more (but I'll check).
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39599958
I'd suggest you try what rorya offers through the link to bytes.com, this might truly work...
0
 
LVL 22

Author Comment

by:spattewar
ID: 39634649
Hi,

sorry to get back so late. I use Alex's solutions for my purpose.

Thank you very much for your time.

Regards.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question