Recordset.Edit or Update sql vba statement fastest way to update?

Question

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 

Answer 1

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.

Answer 2

The SQL method is usually the fastest for bulk updates, but syntax is often clumsy.

The VBA method, however, has the distinct advantages, that code is cleaner, and the recordset can be used before or after the update/edit without requering the data. This can make a huge difference if you have to do long-winded calculations between updates. Also, the recordset can be passed ByRef to supporting functions or further processing.