Solved

Help with calling SQL Stored Procedure from Access

Posted on 2010-08-25
15
838 Views
Last Modified: 2013-11-27
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.

Thanks.
0
Comment
Question by:ebooyens
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 142

Expert Comment

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

should be:

 sqlExpr = "EXEC getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID
0
 
LVL 4

Author Comment

by:ebooyens
Comment Utility
Thanks, then I get "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Sorry
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
how about using adodb command to setup and run your stored procedure? code example can be found here with one that takes arguments
http://www.freevbcode.com/ShowCode.Asp?ID=3687
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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

0
 
LVL 4

Author Comment

by:ebooyens
Comment Utility
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
0
 
LVL 4

Author Comment

by:ebooyens
Comment Utility
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?!  ;)

Thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Author Comment

by:ebooyens
Comment Utility
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?

Thanks!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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.
0
 
LVL 4

Author Comment

by:ebooyens
Comment Utility
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.

Thanks!
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
Comment Utility
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

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

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"

    Else

        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

0
 
LVL 4

Author Comment

by:ebooyens
Comment Utility
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

Thanks!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
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 & ")"
0
 
LVL 4

Author Comment

by:ebooyens
Comment Utility
Yeah, should have tried that.  Thanks a lot, works now!  This is great!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
Comment Utility
No worries.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now