I have created this report that uses Pivot tables and Microsoft Query
to populate the Excel file and extract data from a Oracle database. To
increase the security in these reports, I have created a stored
procedure which when run populates a temp table. on which I run the
select statement.

I have been able to run both the commands individually but not one
after the other in Microsoft Query. Can someone please advise me on how
I might be able to run these one after the other. Thank you.

A

I have created this report that uses Pivot tables and Microsoft Query
to populate the Excel file and extract data from a Oracle database. To
increase the security in these reports, I have created a stored
procedure which when run populates a temp table. on which I run the
select statement.

I have been able to run both the commands individually but not one
after the other in Microsoft Query. Can someone please advise me on how
I might be able to run these one after the other. Thank you.

Unfortunately, I don't think it's possible to use MS Query to get data
from a stored procedure. Perhaps you could create a View instead, and
give it read-only permissions?

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev

Well i am not looking for it to return data to Excel, i just want to be
able to run two sql queries one after the other in the same session.
The data from the stored procedure is going to a temp table so in a way
i am just doing a select from a table. For me to populate that table i
need to be able to run two commands together. Although the views idea
does sound equally good.

Well i am not looking for it to return data to Excel, i just want to be
able to run two sql queries one after the other in the same session.
The data from the stored procedure is going to a temp table so in a way
i am just doing a select from a table. For me to populate that table i
need to be able to run two commands together. Although the views idea
does sound equally good.

OK, so you probably don't want to go through MS Query then. Instead use ADO to connect to the
server and execute the stored proc. An example that you might be able to adapt is at:

http://www.dicks-blog.com/archives/2...do-parameters/


Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev