Calling a Stored Procedure in Access 2007 from VB 6.0

I need to call a stored procedure that was created in Access 2007 from the program in VB 6.0.

This is the working Stored Procedure on the Access db when you right click -> Design View:

PARAMETERS prodCode Text ( 255 ), orderNum Text ( 255 );
SELECT Sum(FullPrice) AS Expr1
FROM Transacs
WHERE (((Transacs.prodcode)=[prodCode]) AND ((Transacs.ordernum)=[orderNum]) AND ((Transacs.Type)='R'));

This is the code that needs fixing in VB 6.0:

Set TransRs = New ADODB.Recordset
Dim transcommQuery As String
transcommQuery = "execute SP_SumOfTransComm " & prodcode & " " & orderNum

TransRs.Open transcommQuery, db, adOpenDynamic, adLockBatchOptimistic

Line 3: transcommQuery = "execute SP_SumOfTra开发者_高级运维nsComm " & selProdcode & " " & selPolNo is the one I need fixing. How to correctly call the Stored Procedure from there?

You can use Command objects to call stored procedures. For detailed information you can take a look here.

Your code would look something like:

Dim Cmd1 As ADODB.Command
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = db
Cmd1.CommandText = "SP_SumOfTransComm"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Append Cmd1.CreateParameter(, adVarWChar, , 255, prodcode)
Cmd1.Parameters.Append Cmd1.CreateParameter(, adVarWChar, , 255, orderNum)
Dim TransRs As ADODB.Recordset
Set TransRs = Cmd1.Execute()

Your parameters should be enclosed in quotes and separated by commas e.g.

transcommQuery = "EXECUTE SP_SumOfTransComm '@prodcode', '@orderNum';"
transcommQuery = Replace$(transcommQuery, "@prodcode", prodcode)
transcommQuery = Replace$(transcommQuery, "@orderNum", orderNum)

...but then you have to worry about escaping quotes and other such nasties.

Better to use a Command object with strongly-typed Parameter objects and let the OLE DB provider handle sanitizing values. See @MicSim's answer for some example code.

It is working for me now, doing the following:

Set TransRs = New ADODB.Recordset
Dim transcommQuery As String

transcommQuery = "{ call SP_SumOfTransComm('" & prodcode & "','" & orderNum & "') }"

TransRs.Open transcommQuery, db, adOpenDynamic, adLockBatchOptimistic