How to call a SQL stored procedure from a macro

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

Guest

We use Access for the front-end linked to SQL tables to store data. We have
a "random selection" process that has been written as a SQL stored procedure
on the SQL Server. I'd like to use criteria from text boxes in a form to
pass to the stored procedure and run the stored procedure from this same
form...using a button to launch a macro....if at all possible! Anybody know
how to do this??

If not from a macro, can it be done using code through this same form?
 
I don't believe it's possible with a macro, but it's pretty easy to do with
VBA.

Assuming your stored procedure is MySP and it takes 2 parameters @parm1 and
@parm2 (with @parm1 being text and @parm2 being numeric), create a
pass-through query that executes the stored procedure:

Exec MySp @parm1='Text string', @parm2=123

Save that pass-through query as, say, MyPassthrough

In VBA, add code like:

Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

strSQL = "Exec MySp @parm1='" & Value1 & _
"', @parm2=" & Value2

Set qdfPassthrough = CurrentDb().QueryDefs("MyPassthrough")
qdfPassthrough.SQL = strSQL
qdfPassthrough.Execute

This assumes that qdfPassthrough doesn't return a recordset. If it does:

Dim qdfPassthrough As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "Exec MySp @parm1='" & Value1 & _
"', @parm2=" & Value2

Set qdfPassthrough = CurrentDb().QueryDefs("MyPassthrough")
qdfPassthrough.SQL = strSQL
Set rsCurr = qdfPassthrough.OpenRecordset


Note that it's not really necessary that the pass-through query exist in
advance: you can always set the Connect property of qdfPassthrough if you
like. I just thought that you'd want to test the pass-through first!
 
Thanks!! I think this will work, but I'm new to this and am not sure how to
point it to my form to get the parameter values. Can I use the fieldnames
like this:

Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

' Note: Path to text boxes in current database:
Forms![frmCreateRandomsPrintCOCForms]![txtClientNumber]

strSQL = "Exec usp_SelectRandomEmployees @clientNumber='" &
[txtClientNumber] & _
"', @branchNumber='" & [txtBranchNumber] & _
"', @testCount='" & [HowManyForDrugTest] & _
"', @alcoholCount='" & [HowManyForAlcoholTest] & _
"', @alternateCount='" & [HowManyAlternates] & "'"

Set qdfPassthrough = CurrentDb().QueryDefs("MyPassthrough")
qdfPassthrough.SQL = strSQL
qdfPassthrough.Execute

Also, the results are being appended into an existing table by the stored
procedure, so I believe we don't need to return a recordset....does that
sound right?
Thanks again!!!
Susan
 
If you're trying to refer to controls on your form, use
Me.[txtBranchNumber], Me.[HowManyForDrugText] and so on.

If all the stored procedure is doing is appending, then I wouldn't expect it
to return a recordset.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Susan said:
Thanks!! I think this will work, but I'm new to this and am not sure how
to
point it to my form to get the parameter values. Can I use the fieldnames
like this:

Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

' Note: Path to text boxes in current database:
Forms![frmCreateRandomsPrintCOCForms]![txtClientNumber]

strSQL = "Exec usp_SelectRandomEmployees @clientNumber='" &
[txtClientNumber] & _
"', @branchNumber='" & [txtBranchNumber] & _
"', @testCount='" & [HowManyForDrugTest] & _
"', @alcoholCount='" & [HowManyForAlcoholTest] & _
"', @alternateCount='" & [HowManyAlternates] & "'"

Set qdfPassthrough = CurrentDb().QueryDefs("MyPassthrough")
qdfPassthrough.SQL = strSQL
qdfPassthrough.Execute

Also, the results are being appended into an existing table by the stored
procedure, so I believe we don't need to return a recordset....does that
sound right?
Thanks again!!!
Susan


Douglas J. Steele said:
I don't believe it's possible with a macro, but it's pretty easy to do
with
VBA.

Assuming your stored procedure is MySP and it takes 2 parameters @parm1
and
@parm2 (with @parm1 being text and @parm2 being numeric), create a
pass-through query that executes the stored procedure:

Exec MySp @parm1='Text string', @parm2=123

Save that pass-through query as, say, MyPassthrough

In VBA, add code like:

Dim qdfPassthrough As DAO.QueryDef
Dim strSQL As String

strSQL = "Exec MySp @parm1='" & Value1 & _
"', @parm2=" & Value2

Set qdfPassthrough = CurrentDb().QueryDefs("MyPassthrough")
qdfPassthrough.SQL = strSQL
qdfPassthrough.Execute

This assumes that qdfPassthrough doesn't return a recordset. If it does:

Dim qdfPassthrough As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "Exec MySp @parm1='" & Value1 & _
"', @parm2=" & Value2

Set qdfPassthrough = CurrentDb().QueryDefs("MyPassthrough")
qdfPassthrough.SQL = strSQL
Set rsCurr = qdfPassthrough.OpenRecordset


Note that it's not really necessary that the pass-through query exist in
advance: you can always set the Connect property of qdfPassthrough if you
like. I just thought that you'd want to test the pass-through first!
Click to expand...
Click to expand...
 
Back
Top