I recently came across vba update statements and I have been using Recordset.Edit
and Recordset.Update
to not only edit my existing data but to update it.
I want to know the difference between the two: recordset.update
and Update sql Vba
statement. I think they all do the same but I can’t figure which one is more efficient and why.
Example code below:
'this is with sql update statement
dim someVar as string, anotherVar as String, cn As New ADODB.Connection
someVar = "someVar"
anotherVar = "anotherVar"
sqlS = "Update tableOfRec set columna = " &_
someVar & ", colunmb = " & anotherVar &_
" where columnc = 20";
cn.Execute stSQL
This is for recordset (update and Edit):
dim thisVar as String, someOthVar as String, rs as recordset
thisVar = "thisVar"
someOthVar = "someOtherVar"
set rs = currentDb.openRecordset("select columna, columnb where columnc = 20")
do While not rs.EOF
rs.Edit
rs!columna = thisVar
rs!columnb = someOthvar
rs.update
rs.MoveNext
loop
Advertisement
Answer
Assuming WHERE columnc = 20
selects 1000+ rows, as you mentioned in a comment, executing that UPDATE
statement should be noticeably faster than looping through a recordset and updating its rows one at a time.
The latter strategy is a RBAR (Row By Agonizing Row) approach. The first strategy, executing a single (valid) UPDATE
, is a “set-based” approach. In general, set-based trumps RBAR with respect to performance.
However your 2 examples raise other issues. My first suggestion would be to use DAO instead of ADO to execute your UPDATE
:
CurrentDb.Execute stSQL, dbFailonError
Whichever of those strategies you choose, make sure columnc is indexed.