Skip to content
Advertisement

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 for recordset (update and Edit):

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:

Whichever of those strategies you choose, make sure columnc is indexed.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement