How to call sql server stored procedure with parameters from Acces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top