I'm trying to find DAO or ADODB Access 2010 vba code to run this MySQL 5.5 SP:
الجن السحر العين الطاقة المس الحسد الشيطان الطب البديل الرقية
الرقاة Personal Branding أثاث مودرن بدمياط
افضل العطور نقل أثاث بالرياض 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