Solved

Returning multiple recordsets - Excel VBA + ADO

Posted on 2013-10-24
13
1,483 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
  • 5
  • 4
  • 4
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
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
Comment Utility
I know mine works with SQL Server but I've never had occasion to use it with Oracle. :)
0
 
LVL 22

Author Comment

by:spattewar
Comment Utility
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
Comment Utility
A quick Google suggests that for Oracle you may not need the semicolon.
0
 
LVL 22

Author Comment

by:spattewar
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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]
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now