to output Stored Proc/View result to xls via VBA code

[Alika]

In a project I'm working on I need to import a result of SQL Server View (Select-Union All sql-statement) or SQL Server Stored Procedure (the same sql-statement; I'm not sure if I get any benefits having select statement implementing as Stored Procedure, rather then simple View; perhaps there is no benefits in it) to xls document.
In code (below) I established connection to SQLServer and than trying to have sp_ActiveAccounts outputting in xls file. the window Output To pops up. When I click OK I got an error message: "The MS Jet Engine can not find the object sp_ActiveAccounts". I understand that DoCmd.OutputTo looks for Access database Tables/Queries, and that Jet Engine is active. How can I make it looking for Server's sp_ActiveAccounts Stored Procedure? What part of code have i omitted?
My code:
dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection

Cnxn.Open "DRIVER={SQL Server};SERVER=ServerName;UID=AccessAccount;APP=Microsoft® Windows® Operating System;WSID=test PC;DATABASE=" & strDBName, "LoginName", "password"

DoCmd.OutputTo acOutputStoredProcedure, "sp_ActiveAccounts", "MicrosoftExcelBiff5(*.xls)", "", False, "", 0

thank you all in advance

 

[Tim K.]

Did you see the sp_ActiveAccounts in Access Queries tab? Is this ADP?

If you see the sp_ActiveAccounts in Access, try adding dbo. prefix to its name.

dbo.sp_ActiveAccounts

 

[Alika]

Did you see the sp_ActiveAccounts in Access Queries tab?

No, the name sp_ActiveAccounts is unique. It's a name of Stored Procedure located on Server I'm connecting Access application to. There is no such name in Access Tables or Queries.

P.S. I don't know what ADP stands for. Sorry.

 

[Tim K.]

A Microsoft Access project (.adp) is an Access data file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB component architecture. Using an Access project, you can create a client/server application as easily as a file server application.

In your case, you don't use ADP. You use a normal Access MDB file to connect to SQLServer. Then you might create a passthru query. Check http://support.microsoft.com/default.aspx?scid=kb;en-us;303968&Product=acc2003 for how to create the passthru query.
The SQL should look like this.

exec sp_ActiveAccounts

Save the query called qryPassThru and change the OutputTo method to

DoCmd.OutputTo acOutputQuery, "qryPassThru", "MicrosoftExcelBiff5(*.xls)", "", False, "", 0

 

[Alika]

you might create a passthru query.

Tim, thanks alot ! the application works very efficient with Pass Through Query.
Tahnk you.
Alika