Re: How to run MySQL Stored Procedure in Access VBA Solved
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;PASSWORD=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;PASSWORD=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