MySQL Forums
Forum List  »  Microsoft Access

Re: MS Access front end - MySQL back end
Posted by: Daniel Hofer
Date: May 11, 2012 10:24AM

This is a solution to my previous post:

Change the Sub-Sub-Form UNITS field of the lineitem and refresh so that the record is not dirty. Then run the stored procedure to recalc the new lineitem extended total and update all other related fields in the parent tables and refresh the Parent form.

Code:

*********************************************************
Private Sub UNITS_AfterUpdate()
On Error GoTo UNITS_AfterUpdate_Err

If [UNITS] = [UNITS].OldValue Then
Exit Sub
End If

Refresh

Form_frmJobs.RecalcValuesViaStoredProcedure

Me.Parent.Parent.Refresh

UNITS_AfterUpdate_Err_Exit:
Exit Sub

UNITS_AfterUpdate_Err:
MsgBox Error$
Resume UNITS_AfterUpdate_Err_Exit
End Sub

*********************************************************
Public Sub RecalcValuesViaStoredProcedure()
Dim cmdStr As String
Dim Pid As Long

Pid = [PROJID]
cmdStr = "sp_updatelijobprojvalues(" & Pid & ")"
RunMySQLStoredProcedure (cmdStr)

End Sub

Public Sub RunMySQLStoredProcedure(cmdStr As String)
Dim cmd As ADODB.Command
Dim Pid As Long
Dim strConn, strServer, strDrive As String
'Dim t1, t2 As Date
't1 = Now()

strServer = TempVars![MySQLServer]
strDrive = Nz(TempVars![AccessDrive], "H")
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PASSWORD=xxxxxxx;SERVER=" & strServer & ";FILEDSN=" & strDrive & ":\AccessHdb\HDB\HDB.dsn;"

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = strConn
.CommandText = "CALL " & cmdStr
.CommandType = adCmdText
.Execute
End With

't2 = Now()
'MsgBox ("Done, " & CStr(Minute(t2 - t1) * 60 + Second(t2 - t1)) & " seconds")

Set cmd = Nothing

End Sub

*********************************************************
DELIMITER $$

CREATE DEFINER=`hdbmysqluser`@`%` PROCEDURE `sp_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 jobs
SET jobs.EARNED = jobs.VALUE * jobs.PCENTDONE/100
WHERE jobs.PROJID = pid;

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;

UPDATE projects RIGHT JOIN
(SELECT jobs.PROJID, SUM(jobs.value) AS SumOfJobValues, SUM(jobs.EARNED) AS SumOfJobEarned FROM jobs
WHERE jobs.PROJID = pid) as temp
ON projects.PROJID = temp.PROJID
SET projects.PERCENT = SumOfJobEarned*100.00/SumOfJobValues;

UPDATE projects
SET projects.WIP = projects.PERCENT*projects.VALUE/100.
WHERE projects.PROJID = pid;


END

Since I'm still porting an old Paradox desktop database to Access2010/MySQL5.5, I haven't done any multi-user testing yet.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MS Access front end - MySQL back end
2631
May 11, 2012 10:24AM


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.