Modifying a ADO recordset without modifying the table

[jnr]

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?

[Granty]

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.

[jnr]

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.

[brucevde]

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

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

[jnr]

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?