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 

Lymphangial

How many rows are selected by WHERE columnc = 20? One row? A million rows? – Arkansas

let's say a 1000 plus – Lymphangial

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.

Arkansas

And if it selects only 1 or a few rows, the update by Recordset would be faster, right? – Oxymoron

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.

Silken

Answer 3

I have found that when I need to update every record in a table in order, such as adding a sequential ID when using Autonumber is not feasible, adding a running total, or any calculation that is incremental based on some value in the recordset, that the DAO method is much faster.

If your data is not in the order you need it processed in, and you instead need to rely on matching values to the data source, then SQL is much more efficient.

Blynn

Answer 4

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select invoice_num from dbo_doc_flow_data where barcode = '" & Me.barcode_f & "'")
Do While Not rs.EOF
            rs.Edit
            rs!invoice_num = Me!invoice_num_f
            rs.Update
            rs.MoveNext
Loop
            rs.Close

Jerad

This does not answer the question. Did you want to ask a question? Then please do that and ask a new question. But read How to Ask first. – Circumbendibus