Difference between UPDATE and .Edit/.Update (1 Viewer)

Hello1 · Apr 20, 2019

Hey guys,
Im currious whats the difference between these 2, also what and when to use?

Code:

Set Cust = MyDb.OpenRecordset("SELECT CustomerID FROM Customer)
                If Cust.RecordCount <> 0 Then
                        Cust.MoveFirst
                        While Not Cust.EOF
                            MyDb.Execute ("UPDATE Receipt SET Receipt.Printed = Date() WHERE Receipt.CustomerID = " & Cust!CustomerID & ";")
                                Cust.MoveNext
                        Wend
                End If

Code:

Set Cust = MyDb.OpenRecordset("SELECT CustomerID FROM Customer)
                If Cust.RecordCount <> 0 Then
                        Cust.MoveFirst
                        While Not Cust.EOF
        Set ReceiptPrint = MyDb.OpenRecordset("SELECT * FROM Receipt WHERE CustomerID = " & Cust!CustomerID & "")
                                With ReceiptPrint
                                        .Edit
                                                !Print = Date
                                        .Update
                                End With
                                Cust.MoveNext
                        Wend
                End If

The example is pretty poor but just curious whats the difference and when what to use.
Thanks!

isladogs · Apr 20, 2019

Both do the same thing but the UPDATE SQL will be faster - much faster if you have a lot of records
With the recordset you are looping through one row at a time.
The update statement effectively processes all records at the same time

Don't use recordsets when a query can do the job perfectly well

Hello1 · Apr 20, 2019

Thats what I recognized and changed to UPDATE SQL but wanted to be sure it does the same job.
Thanks!

pbaldy · Apr 20, 2019

To clarify, the UPDATE SQL as you're using it won't be that efficient. Colin means that you should use it without the recordset and without criteria so that it updates all records in one go:

MyDb.Execute ("UPDATE Receipt SET Receipt.Printed = Date()")

isladogs · Apr 20, 2019

Thanks Paul. That's exactly what I meant though with hindsight I didn't make it clear!

Hello1 · Apr 20, 2019

&#55358;&#56596; So I better use the second option if I need some criteria, or there is something else I can do?

isladogs · Apr 20, 2019

No. Scrap the recordset.
Just use an update query/SQL with or without filter criteria.

CurrentDb.Execute "UPDATE Receipt SET Receipt.Printed = Date() WHERE FieldName = 'some value'"

There is absolutely no reason to loop through your data 'row by agonising row' (RBAR) just to update a date field.

Hello1 · Apr 20, 2019

Ok that was a really bad example I put.
Later when I'm on my laptop I will try to put a better example.

isladogs · Apr 20, 2019

The same principle applies wherever you are updating many records in the same way. Recordsets have their uses. This isn't one of them.

Difference between UPDATE and .Edit/.Update (1 Viewer)

Hello1

Registered User.

isladogs

MVP / VIP

Hello1

Registered User.

pbaldy

Wino Moderator

isladogs

MVP / VIP

Hello1

Registered User.

isladogs

MVP / VIP

Hello1

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread