Accessing SQL Server Stored Procedure from Access

[Guest]

Hi,
I have Access 97 from where I need to run sql server stored procedure by
passing parameters to get the recordsets. Is there any article or concept I
can get
to do that? Any help is appreciated. Thanks

 

[Douglas J. Steele]

The only way that I'm aware of is to create a pass-through query that calls
the stored procedure, passing the parameters.

The SQL of your query will look like:

EXEC MyStoredProcedure @Parm1=42, @Parm2='towel'

To be able to change the parameters, you need to change the SQL of the
stored querydef object. Something like the following untested air-code
should work:

Dim qdfCurr As QueryDef
Dim strSQL as String

strSQL = "EXEC MyStoredProcedure @Parm1=" & Me.txtNumber & _
" @Parm2='" & Me.txtWord & "'"
Set qdfCurr = CurrentDb().QueryDefs("MyQuery")
qdfCurr.SQL = strSQL