Skip navigation links

MySQL Forums :: Microsoft Access :: hello

Advanced Search

Posted by: haytham zalta ()
Date: June 28, 2013 08:42AM

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)
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;

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
hello 1485 haytham zalta 06/28/2013 08:42AM

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.