I can execute a MySQL SP in MySQL but not from Access 2010 vba code.
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$$
Sample MySQL execution that works: Call updatelineitems(3112163);
I've searched for how to execute this in Access 2010 vba, but have not been able to modify any DAO OR ADODB 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
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)