Help with calling SQL Stored Procedure from Access

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
        MsgBox (sqlExpr)
        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.

Who is Participating?
Leigh PurvisConnect With a Mentor Database DeveloperCommented:
Well, if you paste the code below into a new module and then you are free to call it at will.
For example using:
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strSQL As String

    strSQL = "EXEC getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID
    Set qdf = fCreatePassThrough("", strSQL, "YourDBName", "YourServerName", True)
    Set rst = qdf.OpenRecordset
    Debug.Print rst(0)     'Do whatever you need to here

    Set rst = Nothing
    Set qdf = Nothing
See if that makes sense to you.

Function fCreatePassThrough(strName As String, strSQL As String, strDBname As String, _
                        strServer As String, Optional blnIntegratedSecurity As Boolean, _
                        Optional strUserName As String, Optional strPassword As String, _
                        Optional blnReturnsRecords As Boolean = True, Optional strDriver As String = "{SQL Server}") As DAO.QueryDef
    Dim db As Database
    Dim qdf As QueryDef
    Dim strConnect As String
    strConnect = "ODBC;Driver=" & strDriver & ";Server=" & strServer & ";Database=" & strDBname
    If blnIntegratedSecurity Then
        strConnect = strConnect & ";Trusted_Connection=Yes"
        If Len(strUserName) > 0 And Len(strPassword) > 0 Then
            strConnect = strConnect & ";UID=" & strUserName & ";PWD=" & strPassword
        End If
    End If
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(strName)

    With qdf
        .ReturnsRecords = blnReturnsRecords
        .Connect = strConnect
        .sql = strSQL
        .ODBCTimeout = 60
    End With
    Set fCreatePassThrough = qdf
    Set qdf = Nothing
    Set db = Nothing
End Function

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
sqlExpr = "PROCEDURE getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID

should be:

 sqlExpr = "EXEC getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID
ebooyensAuthor Commented:
Thanks, then I get "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

how about using adodb command to setup and run your stored procedure? code example can be found here with one that takes arguments
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Set db = CurrentDb()

refers to the current access db, not the sql server db behind, so you need:

        Dim sqlExpr As String
        Dim rs As ADODB.Recordset
        sqlExpr = "EXEC getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID
        MsgBox (sqlExpr)
        Set rs = CurrentProject.Connection.Execute(sqlExpr)

Open in new window

ebooyensAuthor Commented:
Thanks angellll, with that I get

"The Microsoft Jet database engine cannot find the input table or query 'getWastePrice'. Make sure it exists and that its name is spelled correctly"

I'm using an ODBC connection for the linked tables with SQL Native Client driver.  What does CurrentProject.Connection refer to exactly?

Thanks rockiroads, I'll have a read
ebooyensAuthor Commented:
rockiroads, could you help me with this line using the approach from the article you provided?

Set objPara = objCom.CreateParameter("ShipDate", adDBDate, adParamInput, , Forms![frmOutgoing]![ShipDate].Value)

From doing some more reading I guessed the data type needs to be adDBDate but that shows as format yyyymmdd when I'm passing through dd/mm/yyyy

If I leave data type out I get stuck on objCom.Parameters.Append objPara

If I make it adDBDate I get stuck on Do While Not objRS.EOF with "operation not allowed when the object is closed"

Shees, what have I got myself into?!  ;)

Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you get that error with exactly that code? or did you modify?

CurrentProject.Connection > refers to the underlying sql server database, without odbc connection

I do presume that the procedure is actually a ms sql server procedure, and not a ms access proc?
ebooyensAuthor Commented:
I copied and pasted so exactly that code

Yes it's an MS SQL Server stored procedure being called from VBA code in an Access project

Where would I define the underlying SQL server connection outside of ODBC?

Leigh PurvisDatabase DeveloperCommented:
Well, well... Rocki!
It's been a long time.  How goes things?
I'm taking it as given that this isn't an ADP.
I'm concluding this because you've referenced CurrentDb succcessfully. (You wouldn't have in an ADP).
Consequently both CurrentDb and CurrentProject.Connection are both pointing to the running instance of your Jet/ACE database.
If you're unfamiliar with ADO, it might be simpler to just implement a Passthrough.
However bear in mind that the results of which will be read only.
It depends upon your intended use - and the role the SP plays too.
ebooyensAuthor Commented:
ADP?  No clue sorry.

Could you give me an example of how Passthrough would work with the two parameters?

All I'm trying to do is send a date and a key/ID for a waste item to a stored procedure.  The sp works fine - it gets the price of the waste item on that date, happy days.  Now I just want to send SQL the date ( Forms![frmOutgoing]![ShipDate]) and the waste ID (HazWasteID) from Access and get SQL to return the price.

I can't foresee that I will be using SPs anytime soon to update data so I'm happy with read-only.

ebooyensAuthor Commented:
Thanks LPurvis, that's great and very useful for reuse.

Almost there, just one more thing, sure it's just something simple:

At "Set rst = qdf.OpenRecordset" I now get "Pass-through query with ReturnsRecords property set to True did not return any records"

I've inserted a msgbox before that to validate the strSQL and it comes through what I would expect - EXEC getWastePrice '02/02/2010', 90

To be clear this is a Scalar-Valued Function and if I run this from SQL I get a value back

SELECT dbo.getWastePrice('02/02/2010',90)

I get 720.00

Leigh PurvisDatabase DeveloperCommented:
Why try to change the way you're calling it?
If it's a function - then you call it as
SELECT dbo.getWastePrice('02/02/2010',90)
Just as you have already in Management Studio.
You use exactly the same syntax in your Access code.
strSQL = "SELECT dbo.getWastePrice('" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID & ")"
ebooyensAuthor Commented:
Yeah, should have tried that.  Thanks a lot, works now!  This is great!
Leigh PurvisDatabase DeveloperCommented:
No worries.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.