CODEWITHSUNDEEP
vbams-access
mukeshesic
mukeshesic

Reputation: 77

VBA RecordSet function takes too much time to update record using RecordCount

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

Answers (1)

Strawberryshrub
Strawberryshrub

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

Related Questions