Re: How to run MySQL Stored Procedure in Access VBA?

[Daniel Hofer]

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