Create Recordset not bound to table or view? 1

[CraigBest]

Need some help...

I have a grid in my program that is filled from a read-only ADO SQL Query. On the grid there is a checkbox column I want the user to be able to click on to select/deselect a row. The data is used locally and changes should not update the underlying view. The checkbox does not have a corresponding field in the view, is is just defined as "0 as ChkBox" in the select statement of the query.

When I try to click the checkbox at runtime and change its value from 0 to 1 it causes an error, looks like because the query is read-only.

I think what I'd like to do is copy the ADO recordset to another that isn't directly linked to the database view, so I can make changes to it that won't be reflected in the view.

Is this as simple as cloning the recordset or is there something else I have to do?

Thanks for any help you can provide.

 

[fixthebug]

Yes you can do something like this!

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.ActiveConnection = Nothing
.Fields.Append "EMPNO", adInteger, 6, adFldMayBeNull
.Fields.Append "ENAME", adVarChar, 25, adFldMayBeNull

.Open

.AddNew
.Fields("EMPNO&quot.Value = 1122
.Fields("ENAME&quot.Value = "Jack"
End With

 

[CraigBest]

Thanks FixTheBug,

But I needed to populate the recordset from the data source first. I did eventually figure out the key was to set the active connection property of the recordset to nothing.

Thanks for the tip though. Appreiciate it.

 

[CCLINT]

Then do this:

1. Do NOT open the recordset as ReadOnly
2. Use a Client side cursor and adLockOptimistic (do not use adLockBatchOptimistic and only use the .Update method to update the client side recordset - this will NOT update the underlying table, for this you would need to use adLockBatchOptimistic and the .UpdateBatch method when using a client side cursor)
3. Optionally, after loading the data you can also disconnect the recordset and still work with it:
Set myRS.ActiveConnection = Nothing