open recordset slow

Question

open recordset slow

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 1

If you can change the code that updates each record into an UPDATE statement that runs against the table, you will get a massive improvement in speed

e.g. This loops through all records and multiplies a value by 10

Set rs1 = New ADODB.Recordset

With rs1

.Open "LargeTable", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Do While Not .EOF

.Edit

![ValueField] = ![ValueField] * 10

.Update

.Movenext

Loop

.close

End With

Set rs1 = Nothing

This can be accomplished with a single SQL statement

Currentdb.Execute "UPDATE LargeTable SET ValueField = ValueField * 10", dbFailOnError

klymster Say Hey Kid Points: 704 More actions · Answer 2

Thanks Chris. I'll review my recordset loops and detail and attempt to change them to UPDATE sql. In your experience, can most/all recordset looping operations be changed to a single UPDATE statement?

Chris Quinn-821458 Hall of Fame Points: 3192 More actions · Answer 3

Many times you can, but sometimes you have to do it row by row