Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Returning multiple recordsets - Excel VBA + ADO

Posted on 2013-10-24
13
Medium Priority
?
1,716 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
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 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 2000 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
Technology Partners: 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 14

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 14

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 14

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 14

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

972 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