Reputation: 77
I have one table and one query. Both have the same data field but table COLUMN names are equal to query's ROW name. I update table from query's row data using the following code successfully but it takes too much time to update as there are more than 50 columns name in the table for each employee-
Set rst1 = CurrentDb.OpenRecordset("SELECT * FROM tblPayRollDataTEMP")
Set rst2 = CurrentDb.OpenRecordset("SELECT * FROM qryEmpVerifySalary ")
Do Until rst1.EOF
rst2.MoveFirst
Do Until rst2.EOF
For l = 0 To rst1.Fields.count - 1
If rst1!EmpID = rst2!EmpID And rst1.Fields(l).Name = rst2!Head And rst1!PayBillID = TempVars!BillID Then
With rst1
rst1.Edit
rst1.Fields(l).Value = rst2!Amount
rst1!totDeductions = DSum("Amount", "qryEmpVerifySalary", "[PayHeadType] = 'Deductions' AND [EmpID] = " & rst2!EmpID & "") + DLookup("NPS", "qryEmpPayEarning", "[EmpID] = " & rst2!EmpID & "")
rst1!totRecoveries = DSum("Amount", "qryEmpVerifySalary", "[PayHeadType] = 'Recoveries' AND [EmpID] = " & rst2!EmpID & "")
rst1!NetPayable = rst1!totEarnings - (Nz(rst1!totDeductions, 0) + Nz(rst1!totRecoveries, 0))
rst1.Update
End With
End If
Next
rst2.MoveNext
Loop
rst1.MoveNext
Loop
Set rst1 = Nothing
Set rst2 = Nothing
How to improve the performance of the code?
Upvotes: 0
Views: 160
Reputation: 3399
You should use a query to update your records. This would be the fastest solution. Normally one would match the EmpID
and drag and drop the fields into the update query or use an expression. If you have to group before or other complex stuff split it in more querys (two or three). It doesnt matter thou, because in the end you just execute one update query.
For your code, you can replace the domainaggregate functions. DLookup()
, DSum()
, etc... these are worst for performance. A simple select statement runs way faster than DLookup()
. Here are a few replacements:
Function DCount(Expression As String, Domain As String, Optional Criteria) As Variant
Dim strSQL As String
strSQL = "SELECT COUNT(" & Expression & ") FROM " & Domain
'Other Replacements:
'DLookup: strSQL = "SELECT " & Expression & " FROM " & Domain
'DMax: strSQL = "SELECT MAX(" & Expression & ") FROM " & Domain
'DMin: strSQL = "SELECT SUM(" & Expression & ") FROM " & Domain
'DFirst: strSQL = "SELECT FIRST(" & Expression & ") FROM " & Domain
'DLast: strSQL = "SELECT LAST(" & Expression & ") FROM " & Domain
'DSum: strSQL = "SELECT SUM(" & Expression & ") FROM " & Domain
'DAvg: strSQL = "SELECT AVG(" & Expression & ") FROM " & Domain
If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria
DCount = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenForwardOnly)(0)
End Function
Upvotes: 1