Execute MySQL Stored Procedure with Access 2010 VBA?

[DanielHofer]

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)

[DanielHofer]

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

[DanielHofer]

Until I get help on executing a MySQL SP in VBA, I'm using this instead.


Access query: qryUpdateLineitems


PARAMETERS pid Long;
UPDATE lineitems RIGHT JOIN (SELECT lineitems.ID, jobs.JOBNO, lineitems.EXTTOTAL, PRICE*UNITS*IIf(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;


ADO Code:

Public Sub RecalcValues()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim pid As Long
pid = [PROJID]

'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = cn
.CommandText = "EXECUTE qryUpdateLineitems " & pid
.Execute
End With

Set cmd = Nothing
Set cn = Nothing

Me.Refresh

End Sub

[orange]

What is the difference in execution time -- direct MySQL vs your ADO code?

[DanielHofer]

Executing Call updatelineitems(3112163); in MySQL 5.5 takes 1.217 sec to update 3 lineitems.

Clicking an Access 2010 button that runs ReCalcValues() for the same 3 lineitems takes 2 seconds.

I tried to run the same query with all 138,994 lineitems in my db, but MySQL Workbench 5.2.36 Revision 8542 has connection timeout hardwired to 600 seconds, which is where my query lost connection.

A ProjId with 20 lineitems took 7.425 sec Workbench, Access took 6 seconds over a business network and 2 seconds over a home network.

I don't know how linear doing all the lineitems would be, but based on the 2 short queries, it would take 14 hours.

My background is Borland's Paradox 7, so I've probably got a lot of client-server learning to do.

[DanielHofer]

Problem was to update values in 3 tables, 1 project to n jobs to n lineitems.

MySQL 5.5 Stored Procedure:
USE `hdb`;
DROP procedure IF EXISTS `updatelijobprojvalues`;
DELIMITER $$
USE `hdb`$$
CREATE PROCEDURE `hdb`.`updatelijobprojvalues` (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;

UPDATE jobs RIGHT JOIN
(SELECT lineitems.JOBNO, Sum(lineitems.EXTTOTAL) AS SumOfEXTTOTAL FROM lineitems
WHERE lineitems.JOBNO IN (SELECT jobs.JOBNO FROM jobs WHERE jobs.PROJID=pid)
GROUP BY lineitems.JOBNO) as temp
ON jobs.JOBNO = temp.JOBNO
SET jobs.VALUE = temp.SumOfEXTTOTAL;

UPDATE projects RIGHT JOIN
(SELECT jobs.PROJID, SUM(jobs.value) AS SumOfJobValues FROM jobs
WHERE jobs.PROJID = pid) as temp
ON projects.PROJID = temp.PROJID
SET projects.VALUE = SumOfJobValues;

END
Note 2nd and 3rd queries cannot be done in Access except as Pass Through queries due to sum aggregation.

******************************************
Access 2010 vba with Pass Through Queries:

Public Sub RecalcValuesViaPassThroughQueries()
Dim qd As DAO.QueryDef
Dim db As Database
Dim pid As Long
Dim strSQL As String
Dim strConn As String
Dim strServer As String
pid = [PROJID]
Dim t1, t2 As Date
t1 = Now()

Set db = CurrentDb()
strServer = TempVars![MySQLServer]

If Not IsNull(db.QueryDefs("qd").SQL) Then 'doesn't exist
CurrentDb.QueryDefs.Delete "qd"
End If

Set qd = db.CreateQueryDef("qd")
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PAS SWORD=x;SERVER=" & strServer & ";FILEDSN=C:\AccessHdb\HDB.dsn;"
qd.Connect = strConn
strSQL = "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;"
qd.SQL = strSQL
qd.ReturnsRecords = False
qd.Close

Application.RefreshDatabaseWindow
DoCmd.OpenQuery "qd", acViewNormal, acReadOnly

strSQL = "UPDATE jobs RIGHT JOIN " _
& "(SELECT lineitems.JOBNO, Sum(lineitems.EXTTOTAL) AS SumOfEXTTOTAL FROM lineitems " _
& "WHERE lineitems.JOBNO IN (SELECT jobs.JOBNO FROM jobs WHERE jobs.PROJID=" & pid & ") " _
& "GROUP BY lineitems.JOBNO) as temp " _
& "ON jobs.JOBNO = temp.JOBNO " _
& "SET jobs.VALUE = temp.SumOfEXTTOTAL;"
qd.SQL = strSQL
qd.ReturnsRecords = False
qd.Close

Application.RefreshDatabaseWindow
DoCmd.OpenQuery "qd", acViewNormal, acReadOnly

strSQL = "UPDATE projects RIGHT JOIN " _
& "(SELECT jobs.PROJID, SUM(jobs.value) AS SumOfJobValues FROM jobs " _
& "WHERE jobs.PROJID = " & pid & ") as temp " _
& "ON projects.PROJID = temp.PROJID " _
& "SET projects.VALUE = SumOfJobValues;"
qd.SQL = strSQL
qd.ReturnsRecords = False
qd.Close

Application.RefreshDatabaseWindow
DoCmd.OpenQuery "qd", acViewNormal, acReadOnly

t2 = Now
Me.Refresh

qd = Nothing
db = Nothing
MsgBox ("Done, " & CStr(Minute(t2 - t1) * 60 + Second(t2 - t1)) & " seconds")

End Sub

*************************************
Access 2010 vba calling a MySQL Stored Procedure

Public Sub RecalcValuesViaStoredProcedure()
Dim cmd As ADODB.Command
Dim pid As Long
Dim strConn, strServer As String
Dim t1, t2 As Date
t1 = Now()
strServer = TempVars![MySQLServer]
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PAS SWORD=x;SERVER=" & strServer & ";FILEDSN=C:\AccessHdb\HDB.dsn;"
pid = [PROJID]
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = strConn
.CommandText = "CALL updatelijobprojvalues(" & pid & ")"
.CommandType = adCmdText
.Execute
End With

t2 = Now()
MsgBox ("Done, " & CStr(Minute(t2 - t1) * 60 + Second(t2 - t1)) & " seconds")
Me.Refresh
Set cmd = Nothing

End Sub