Skip navigation links

MySQL Forums :: Microsoft Access :: How to run MySQL Stored Procedure in Access VBA?


Advanced Search

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? 4535 Daniel Hofer 01/16/2012 01:38AM
Re: How to run MySQL Stored Procedure in Access VBA? 2094 Daniel Hofer 01/18/2012 10:06AM
Re: How to run MySQL Stored Procedure in Access VBA Solved 4223 Daniel Hofer 01/23/2012 01:19PM
jclaborn23 1153 Jerry Claborn 02/23/2012 09:51AM
Re: How to run MySQL Stored Procedure in Access VBA Solved 1388 Daniel Hofer 02/27/2012 06:40AM


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.