SQL stored procedure in Access output to Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a stroed procedure that I am running within Access through a macro and
Module1 VB. The stored procedure is running just fine. But it is creating
#temp tables. I want to query one of those #temp tables and output the
results to Excel. How do I do that???

Any help would be greatly apprciated.

Thanks in advance.
 
I just want to be sure we are using the same definition for "stored
procedure"...

Are you saying your db is connected to/using SQL Server (or some other
backend), rather than Access to store your data? Stored procedures
generally mean code written in a SQL-Server-like backend.

?You are creating temp tables? Any chance you could: 1. write a query that
returns the records you wish to see, then 2. export the query (results) to
Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes Jeff. Stored Procedures meaning SQL Server Stored Procedures.

That being said, how would I write a SQL Query within this stored procedure
and output the result to Excel???
 
Take a look in Access HELP for "pass-through query" information.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
have you tried using the Data> Import External Data > New Database Query in
Excel?? I use it all the time and it sounds like it would be something you
could use. It will bring up a query analyzer type application in Excel that
you can write your SQL in
 
wnfisba said:
I have a stroed procedure that I am running within Access through a
macro and Module1 VB. The stored procedure is running just fine. But
it is creating #temp tables. I want to query one of those #temp
tables and output the results to Excel. How do I do that???

Any help would be greatly apprciated.

Thanks in advance.
Click to expand...

It sounds to me like you need to:

(a) In SQL Server, design the SP to return the contents of the table/

(b) In Access, create a pass-through query that executes the SP and
returns the records.

(c) In Access, in your VB code, use DoCmd.TransferSpreadsheet to export
that pass-through query to Excel.
 
Back
Top