How to call sql server stored procedure with parameters from Acces

[Jack]

Hi,
I am building an Access application where I need to call sql server stored
procedure with parameters from access and pass those parameters in Access.
Any article or knowledge base will be of great help. Thanks

 

[Alex Dybenko]

Hi,
you need to run pass-through query, you can dynamically build it SQL as:

currentdb.querydefs("MyQuery").SQL="MySP param1, 'param2'"

to get data back from SP - you can add Select @param1, @param2 at the end of
it and query will return them

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Douglas J. Steele]

To call a SQL Server stored procedure, you need to use a pass-through query
with the correct Connect information. The SQL for the query will be
something like:

Exec SPName @NumericParm=123, @TextParm='XYZ'

Unfortunately, there's no way to dynamically get the values from Access: you
have to change the SQL of the query before running it:

Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

strSQL = "Exec SPName @NumericParm=" & _
Me!NumericValue & ", @TextParm='" & _
Me!TextValue & "'"

Set qdfPassthrough = CurrentDb().QueryDefs("SPQuery")
qdfPassthrough.SQL = strSQL