Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with calling SQL Stored Procedure from Access

Posted on 2010-08-25
15
Medium Priority
?
855 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33520201
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
ID: 33520228
Thanks, then I get "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Sorry
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33520280
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33520285
>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
ID: 33521514
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
ID: 33522268
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33523445
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
 
LVL 4

Author Comment

by:ebooyens
ID: 33524826
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
ID: 33524929
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
ID: 33525521
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 2000 total points
ID: 33527447
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
ID: 33529214
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
ID: 33529230
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
ID: 33529254
Yeah, should have tried that.  Thanks a lot, works now!  This is great!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 33529431
No worries.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

609 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