How to export stored procedure with parameter?

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Can someone help me understand how to export a stored
procedure with a parameter to Excel in code without being
prompted for the parameter with an input box? I can
get "DoCmd.OutputTo acOutputStoredProcedure" to work fine
when exporting to Excel without a parameter, but I
absolutely can't figure out the syntax to pass the
parameter through code. Can anyone help?

Whenever I try to append the variable at the end of the
stored procedure, be
it "dbo.usp_Param_Select_Users_by_App " & AppID or
anything else, I get "there was a problem opening the OLE
object". Is there a simple solution to this, like using a
command or recordset object, or am I going about this the
wrong way? I would really like to do this with
the "DoCmd.OutputTo acOutputStoredProcedure" action/method
if possible.

Thanks.

Dim strAppName, strFileName As String
Dim AppID As Integer
cboAllApps.SetFocus
AppID = Forms!frmAppLookupwUsers!cboAllApps.Value
strAppName = Forms!frmAppLookupwUsers!cboAllApps.Column(1)


strFileName = "Users_by_Application_" & strAppName
strFileName = "C:\Temp\" & strFileName & ".xls"


DoCmd.OutputTo acOutputStoredProcedure, _
"dbo.usp_Param_Select_Users_by_App", _
"MicrosoftExcel(*.xls)", _
strFileName, _
True, _
""
 
Back
Top