MySQL Forums
Forum List  »  Microsoft Access

Re: How to run MySQL Stored Procedure in Access VBA Solved
Posted by: Daniel Hofer
Date: January 23, 2012 01:19PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to run MySQL Stored Procedure in Access VBA Solved
9431
January 23, 2012 01:19PM
2467
February 23, 2012 09:51AM


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.