Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL stored procedure in Access output to Excel

Discussion in 'Information Technology' started by wnfisba, Jul 28, 2006.

  1. wnfisba

    wnfisba
    Expand Collapse
    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.
     
  2. Loading...


  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:F27C60E5-B14C-4A5B-8DE8-FF3CFB8B06F6@microsoft.com...
    >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.
     
  4. wnfisba

    wnfisba
    Expand Collapse
    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" wrote:

    > 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
    >
    >
    > "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    > news:F27C60E5-B14C-4A5B-8DE8-FF3CFB8B06F6@microsoft.com...
    > >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.

    >
    >
    >
     
  5. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

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

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:25AFFF5E-28D2-4A85-B683-A467D5D45A0A@microsoft.com...
    > 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" wrote:
    >
    >> 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
    >>
    >>
    >> "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    >> news:F27C60E5-B14C-4A5B-8DE8-FF3CFB8B06F6@microsoft.com...
    >> >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.

    >>
    >>
    >>
     
  6. The Mecca

    The Mecca
    Expand Collapse
    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

    "wnfisba" wrote:

    > 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.
     
  7. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "wnfisba" <wnfisba@discussions.microsoft.com> wrote in message
    news:F27C60E5-B14C-4A5B-8DE8-FF3CFB8B06F6@microsoft.com
    > 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.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  8. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

    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
    dlesandrini@hotmail.com
    http://amazecreations.com/datafast


    "wnfisba" <wnfisba@discussions.microsoft.com> wrote ...
    >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.
     
  9. Danny J. Lesandrini

    Danny J. Lesandrini
    Expand Collapse
    Guest

Share This Page