Modifying a ADO recordset without modifying the table

Hi,

I am using ADO in a VB6 application to select data from a table into a recordset and then present this to the user.

So far so good, no problems there. But...

Before I present it to the user I want to step through the recordset and do update some values in some cells but I do not want these updates to also be applied in the table in my database.

How can I change data in a recordset without it also being changed in the table where it originates from?

Modify in what way? And how are you presenting the data to your user?

If your controls are bound to the recordset then you are stuck I think. If you populate them yourself then you should be able to do all your data manipulation in your populate routines.

Below is what i want to do with the data in the recordset (called rsgrid).
I am stepping thru each record to see if the value in the "spike" column is 1. If spike=1 I want to take that records StopTime and Duration values and and apply those on the previous record. When that is done I want to delete the record that had Spike=1.

Here is the code:

While lngCurrentRecord < rsgrid.RecordCount

If rsgrid!spike= 1 Then

spikestart = rsgrid!StartTime
spikestop = rsgrid!StopTime
spikeduration = rsgrid!Duration
'moving to previous to add time for the spike activity
rsgrid.MovePrevious

rsgrid!Duration = rsgrid!Duration + spikeduration
rsgrid!StopTime = spikestop

'moving forward to spike and delete the spike
rsgrid.MoveNext
rsgrid.Delete

rsgrid.MoveNext
lngCurrentRecord = lngCurrentRecord + 1
Else

rsgrid.MoveNext
lngCurrentRecord = lngCurrentRecord + 1
End If
Wend


All this code is in the OCX i am developing, so when i am later on setting its properties with the StartTime, StopTime and Duration values I want it to have filtered out all rows with Spike=1 according to teh above loop. But I do NOT want this also to happen in the database table.

Disconnect the recordset from the database. If/when you are ready to update just reconnect.

VB Code:

Dim rs as ADODB.Recordset
 
Set Rs = new ADODB.Recordset
 
Rs.CursorLocation =adUseClient ' must be client cursor for disconnected reordsets
 
Rs.Open "Select * From Products", SomeConnection, adOpenStatic, adLockBatchOptimistc
 
Set Rs.ActiveConnection = Nothing
 
'database connection can be closed
'code to update the recordset

Thanks brucevde,

I will try that but I guess that makes sense.

One question though...
You used adOpenStatic and adLockBatchOptimistc as the parameters for the connection.

I was using adOpenKeyset and adLockOptimistic instead.

What will the difference be, or does someone have any good link explaining what the different parameters available?