SQL stored procedure in Access output to Excel

[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.

 

[Jeff Boyce]

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

 

[Guest]

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???

 

[Jeff Boyce]

Take a look in Access HELP for "pass-through query" information.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

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

 

[Dirk Goldgar]

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.

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.

 

[Danny J. Lesandrini]

I wrote an article (which includes a download) for managing SQL Scripts from Access.
One of the features is a button to Execute the Query into Excel. It may be of help.

http://www.databasejournal.com/features/msaccess/article.php/1465761

 

[Danny J. Lesandrini]

Ooops, DBJ removed the link to the download. Here it is from my site ...

http://amazecreations.com/datafast/GetFile.aspx?file=A97CatalogSQL.zip