Post reply

Output stored procedure to Excel from Access 2003 adp

  • March 25, 2009 at 10:36 am

    #75443

    Hi

    I'm running Access 2003 adp to SQL Server 2000 back end. I am trying to output the results of an sp to Excel. I thought I knew how to do this - but Access thought I didn't...

    My code is:

    Dim Sn As String

    Sn = "hind"

    myparam = "exec spLink_Excel '" & Sn & "'"

    DoCmd.OutputTo acOutputStoredProcedure, myparam, "Microsoft Excel (*.xls)", "", True

    When I run this without a parameter it works fine. With a parameter it returns the error message:

    Can’t find the object ‘exec cmdb.dbo.spLink_Excel ‘hind’.’

    I've tried it every which way... Does anybody have any ideas - which will be gratefully excepted.

    Thanks

    Paul

  • March 26, 2009 at 7:52 am

    #966670

    This has just worked for me on one of my stored procedures

    DoCmd.OutputTo acOutputStoredProcedure , "EXEC usp_SelectClientByNumber '00010'", acFormatXLS, "c:\test.xls"

  • March 26, 2009 at 10:54 am

    #966861

    Thanks, Chris

    I've got it working now - after a fashion...

    My sp used the parameter to create a temp table, the values of which were returned and output to Excel. This works fine without the parameter and set nocount on - but not with the parameter. I've had to re-jig it.

    Many thanks again for taking the trouble to reply.

    Paul

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply