How to call sql server stored procedure with parameters from Acces

J

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
 
A

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top