MySQL Forums
Forum List  »  Microsoft Access

How to run MySQL Stored Procedure in Access VBA?
Posted by: Daniel Hofer
Date: January 16, 2012 01:38AM

I'm trying to find DAO or ADODB Access 2010 vba code to run this MySQL 5.5 SP:

CREATE DEFINER=`hdbmysqluser`@`%` PROCEDURE `updatelineitems`(pid LONG)
BEGIN
UPDATE lineitems RIGHT JOIN (SELECT lineitems.ID, jobs.JOBNO, lineitems.EXTTOTAL, PRICE*UNITS*If(USEPRORATE,PRORATEP/100,1) AS NEWEXTTOTAL, jobs.PROJID
FROM (lineitems INNER JOIN jobs ON lineitems.JOBNO = jobs.JOBNO) INNER JOIN biditems ON lineitems.ITEMID = biditems.ITEMID
WHERE (((jobs.PROJID)=pid))) AS prep ON lineitems.ID = prep.Id
SET lineitems.exttotal = newexttotal;
END$$

MySQL execution works ok: Call updatelineitems(3112163);

I've searched for how to execute this in Access 2010 vba, but have not been able to modify any ADODB or DAO examples to work. The closest I get is:

Public Sub RecalcValues()
Dim cnn As New ADODB.Connection
Dim exeStr As String
exeStr = "CALL updatelineitems(" & [PROJID] & ")"

Set cnn = CurrentProject.Connection
cnn.CursorLocation = adUseClient 'also tried adUseServer
If cnn.STATE = ADODB.adStateOpen Then
cnn.Execute exeStr
End If
End Sub

The error msg is:
Invalid SQL Statement; expected 'DELETE', 'INSERT', PROCEDURE', 'SELECT', or 'UPDATE'. In example, exeStr evaluates to: CALL updatelineitems(3112163)

I also tried DAO code to execute sp that fails with the same error msg.
"sptest" is the same sp as "updatelineitems" with the parameter built in.

Dim db As Database
Dim l As Long
Dim Rs As Recordset
Set db = CurrentDb
db.Execute "sptest", dbSQLPassThrough
l = db.RecordsAffected

Options: ReplyQuote


Subject
Views
Written By
Posted
How to run MySQL Stored Procedure in Access VBA?
14915
January 16, 2012 01:38AM
3060
February 23, 2012 09:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.