• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1757
  • Last Modified:

Returning multiple recordsets - Excel VBA + ADO

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
spattewar
Asked:
spattewar
  • 5
  • 4
  • 4
1 Solution
 
Rory ArchibaldCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Rory ArchibaldCommented:
I know mine works with SQL Server but I've never had occasion to use it with Oracle. :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
spattewarAuthor Commented:
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
 
Rory ArchibaldCommented:
A quick Google suggests that for Oracle you may not need the semicolon.
0
 
spattewarAuthor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
spattewarAuthor Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
Rory ArchibaldCommented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I'd suggest you try what rorya offers through the link to bytes.com, this might truly work...
0
 
spattewarAuthor Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now