asked on

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.

Thanks.

Guy Hengel [angelIII / a3]

sqlExpr = "PROCEDURE getWastePrice '" & Forms![frmOutgoing]![ShipDate].Value & "', " & HazWasteID

should be:

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

ebooyens

ASKER

Thanks, then I get "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Sorry

rockiroads

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

Guy Hengel [angelIII / a3]

>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

ebooyens

ASKER

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

ebooyens

ASKER

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

Guy Hengel [angelIII / a3]

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?

ebooyens

ASKER

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!

Leigh Purvis

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.

ebooyens

ASKER

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!

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ebooyens

ASKER

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!

Leigh Purvis

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 & ")"

ebooyens

ASKER

Yeah, should have tried that. Thanks a lot, works now! This is great!

Leigh Purvis

No worries.