With ADO, is the Update Method necessary when updating a field in a table / recordset?

In updating older DAO code that loops through a table in Access and changes the field value, I noticed that I kept getting errors when trying to hold on to the Update Method.  Once I let that go I had no problem with the code.  

Check the functions below - I've included both the old and new code.  I want to be sure that I'm converting this correctly without that method, even if it's working just fine for me now.

Also, did I do the right thing by treating the table as a recordset in the new code?

Thanks!

James
OLD CODE
Set rs = CurrentDb().OpenRecordset("SomeTable", dbOpenDynaset)
Do While Not rs.EOF
  For Each fld In rs.Fields
    rs.Edit
    If fld.Value = "ABC" Then fld.Value = "XYZ" 
    rs.Update
  Next
  rs.MoveNext
Loop
 
 
NEW CODE
SQL = "Select * FROM SomeTable"
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic
rs.CursorLocation = adUseServer
Do While Not rs.EOF
  For Each fld In rs.Fields
    If fld.Value = "ABC" Then fld.Value = "XYZ"
  Next
  rs.MoveNext
Loop

Open in new window

james_axtonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
You don't need to use rs.update for ADO but I think it is accepted practice to do so.
But it should come just before your Movenext command , not inside the field change loop.
0
leonstrykerCommented:
Why do that at all, when this is faster and cleaner:

SQL = "Update SomeTable Set myFieldValue = 'ABC' WHERE myfieldValue ='XYZ'"
cn.Execute SQL

Leon
0
james_axtonAuthor Commented:
leonstryker, only because i need to go across multiple fields - as many as 30 or 40 in some cases.

peter57r, if I did use rs.Update what would be the proper way to use it in the above code marked "NEW"?  I repeatedly hit errors with it until I removed it.    

Thanks,

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

leonstrykerCommented:
You are still better of with a SQL UPDATE than an ADO Update method. Even running 30 or 40 SQL statements in a loop is better.

Leon
0
james_axtonAuthor Commented:
Leon, if you don't mind explaining, why is that better?  One all-encompassing loop takes care of my changes for each field (even when the field names are dynamic) as compared to running two lines for each field like this:

SQL1 = "Update SomeTable Set myFieldValue1 = 'ABC' WHERE myfieldValue1 ='XYZ'"
SQL2 = "Update SomeTable Set myFieldValue2 = 'ABC' WHERE myfieldValue2 ='XYZ'"
SQL3 = "Update SomeTable Set myFieldValue3 = 'ABC' WHERE myfieldValue3 ='XYZ'"
...
cn.Execute SQL1
cn.Execute SQL2
cn.Execute SQL3
...
0
james_axtonAuthor Commented:
To anyone else reading this, I'm still curious about my questions so if you have an input please jump in!  I still want to know:

1) Was it okay to leave the Update method out of the "New" code and if not how should it go in?
2) I am currently leaving the Update method out and the "New" code is working perfectly.  Why is that?
3) Did I do the right thing in treating the table as a recordset in the new code?

I'll increase the points if necessary.

Thanks,
0
leonstrykerCommented:
>Leon, if you don't mind explaining, why is that better?  

Executing a well defined SQL code will be extrimely quick on your database and will carry a very small amount of overhead, even if you have to do it multiple times. Each statement is transactional, there is no possibility of deadlocks or slowing concurant users. With an ADO Update method you will start running into issues with locking and memory overhead for the Recordset.

Besides you can do it all in one call:

SQL1 = "Update SomeTable Set myFieldValue1 = 'ABC' WHERE myfieldValue1 ='XYZ' "
SQL1 = SQL1  & "Update SomeTable Set myFieldValue2 = 'ABC' WHERE myfieldValue2 ='XYZ' "
SQL1 = SQL1 & "Update SomeTable Set myFieldValue3 = 'ABC' WHERE myfieldValue3 ='XYZ' "
...
cn.Execute SQL1

Leon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.