CODEWITHSUNDEEP
sqlvbams-access
Coding Enthusiast
Coding Enthusiast

Reputation: 3933

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

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

Upvotes: 9

Views: 50596

Answers (4)

dcbeckman
dcbeckman

Reputation: 122

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.

Upvotes: 0

Boris
Boris

Reputation: 1

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

Upvotes: -1

Gustav
Gustav

Reputation: 56026

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.

Upvotes: 7

HansUp
HansUp

Reputation: 97131

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.

Upvotes: 9

Related Questions