Deleting records from ADO RS with adUseClient

[jojo11]

I am having trouble deleting records from a client Recordset. Here is how I pull in the recordset.

Dim ItemRS As New ADODB.Recordset
sSql = "Select * From MyTable

If ItemRS.State = 0 Then
With ItemRS
.CursorLocation = adUseClient
.Open Source:=sSql, ActiveConnection:=zLocalConnection, CursorType:=adOpenDynamic, LockType:=adLockOptimistic
End With
End If

This basically is used to load a sheridan datagrid in add item mode. Anyway, to be able to let the user sort I have to use adUseClient. For a sanity check, I guess that this means the application is not hitting the database any longer to get read the data, it is on the client machine. So I could actually delete all of the items in 'MyTable' and the app would still be able to read from ItemRS.
Now, the user selects items from the grid to load into a permanent table in the database. I scroll through the grid and grab all the record infor for each selected item and throw it in the table. Works fine. I then remove the ones that they selected from the grid, but I can't for the life of me remove the items from the ItemRS Recordset.
I have tried using the various ItemRS.Delete adAffectCurrent after filtering for the current record that I want to delete but that doesn't work. I really want to be able to just build an array or something of the ones that I load into the permanent database table and then delete them from the Item RS.
What am I doing wrong.

Thanks

When you have spent all day trying to work through a problem, it is best to take a step back, reevaluate the problem, then work on the next project and never let the old one show its ugly face again!!

 

[robctech]

Well I think you made it more complicated then necassary but here try this way.

Dim ItemRS As New ADODB.Recordset
sSql = "Select * From MyTable

with ItemRS
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .ActiveConnection = zLocalConnection
    .Source = sSQL
    .Open
    .ActiveConnection = Nothing
End with

Now when you want to delete a record simply delete it as you normally would with ItemRS.Delete. Then when you want
to update the actuall database simply reactivate the connection and update as follows

With ItemRS
    .ActiveConnection = zLocalConnection
    .UpdateBatch
End with

I do this CONSTANTLY as the one app I'm working on now I don't want to post the updates until the user says to do so.

Hope this helps and good luck