I have these two sample snippets that practically accomplishes the same thing; I haven't noticed any difference in speed whatsoever. Both are fast enough and does the job really well.
What does it do:
Both snippets will perform a reset values to fields: TotalDays, Debit and PayDate on an active transaction.
A. Looping through recordset
B.Using Update action query
Question:
If both are equally efficient enough in doing the job, which one in best practice should be prioritized to be used when available?
What does it do:
Both snippets will perform a reset values to fields: TotalDays, Debit and PayDate on an active transaction.
A. Looping through recordset
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset _
("SELECT * FROM tblReceivables WHERE TransactionID = " & Me.TransactionID & _
" And Active = -1")
Do Until rs.EOF
With rs
.Edit
!TotalDays = 0
!Debit = 0
!PayDate = Null
.Update
.MoveNext
End With
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
B.Using Update action query
Code:
strSQL = ""
strSQL = strSQL & " UPDATE tblReceivables "
strSQL = strSQL & " SET TotalDays = 0, Debit = 0, PayDate = Null "
strSQL = strSQL & " WHERE TransactionID = " & Me.TransactionID & " And Active = -1 "
CurrentDb.Execute strSQL, dbFailOnError
Question:
If both are equally efficient enough in doing the job, which one in best practice should be prioritized to be used when available?