Help with calling SQL Stored Procedure from Access
Posted on 2010-08-25
Hi all, this is my first attempt at calling a SQL stored procedure from Access to probably doing something really stupid. Been trying to read up on how to call stored procedures and getting conflicting information so this is the best I could come up with:
Dim db As Database
Dim sqlExpr As String
Dim rs As Recordset
Set db = CurrentDb()
sqlExpr = "PROCEDURE getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID
Set rs = db.OpenRecordset(sqlExpr, dbOpenSnapshot, dbSQLPassThrough)
The error message I'm getting is "Syntax error in PARAMETER clause"
Now I've inserted the MsgBox in there so I can see what the sqlExpr value is and it comes back as
PROCEDURE getWastePrice '02/02/2010', 118
Which is what I would expect. So what's wrong with my syntax?
Open to other, better ways of calling stored procedures with parameters that return values.