Editing Data with Recordsets

As in DAO, you edit data in recordsets using the AddNew, Update, and CancelUpdate methods.

You'll notice that we didn't mention the Edit method; that's because it doesn't exist in ADO. In DAO, when you leave a record, any changes are discarded. By contrast, when you leave a record in ADO, the changes are immediately committed. In addition, the ADO Update method is optional. You don't need to use it; however, you'll earn yourself a runtime error if you attempt to close a recordset without committing or cancelling any changes, so I recommend you explicitly use it anyway.

With rs

adOpenDynamic, adLockOptimistic, adCmdTable

'Check that a record exists If .AbsolutePosition > adPosUnknown Then 'ADO does not have an "Edit" method !Phone = "555-5554" .Update End If

'Add a new record .AddNew

!CompanyName = "Ollivanders" !Phone = "555-5555"

If booOK2Save = True Then .Update

Else

.CancelUpdate End If End With

Using the above technique, you can edit records and send the updates to the database one at a time. Of course, you can edit a bunch of records and send the updates all at once, like

With rs

.Open "Shippers", cn, _

adOpenDynamic, adLockOptimistic,

adCmdTable

'Check that a record exists

If .AbsolutePosition > adPosUnknown

Then

'Edit several records

!Phone = "555-5554"

.MoveNext

!Phone = "666-6666"

.MoveNext !Phone "777-7777" .Update End If End With

ADO also allows batch updates, which allows you to edit multiple records and then send them all to the OLE DB provider to be saved as a single operation. To use this feature, you must use a client-side cursor and open the recordset using the adLockBatchOptimistic LockType property.

With rs

.CursorLocation = adUseClient .CursorType = adOpenKeyset .LockType = adLockBatchOptimistic .Open "Customers", cn

'Find the right record to edit .Find "Country = 'USA'" Do While Not .EOF

'Edit the current record !Region = "AA"

'Skip over the current record to 'find the next matching record .Find "Country = 'USA'", 1

Loop

'Commit all the changes .UpdateBatch End With

0 0

Post a comment