Really disconnect a recordset?

(Originally posted in microsoft.public.data.ado)

Is there a way to completely disconnect a recordset so that it has no memory
of its server origins? So that things like
rs.Field(0).Properties("BASETABLENAME") will be blank? So it doesn't know
it was created via OLE DB for SQL Server? So that it won't validate that
"hey you really can't update that on the server"?

I've got a recordset that comes from a query where some of the fields are
derived and not actual table fields. On the client side, I want to be able
to update those fields in the local recordset without ever doing an update
back to SQL Server. Because of the underlying schema and dynamic
properties, ADO is not allowing me to "update" the these fields.

Basically, I want to take the data from this recordset and put it into
another recordset with an identical "shallow" schema -- meaning same column
names and column types but none of the other attributes that link it to the
provider, so that I can manipulate it like a local-only recordset?

Do I basically need to create a new recordset by looping through the old
one's field defs and then copy the rows one by one?

Try this from VB6 with a reference to ADO 2.7:

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.CursorLocation = adUseClient
con.Open "Driver={SQL
Server};Server=(local);Database=master;Trusted_Connection=true"
rs.Open "SELECT 1 AS One", con, adOpenStatic, adLockOptimistic
Set rs.ActiveConnection = Nothing
con.Close
Set con = Nothing
rs("One") = 2 ' BOOOOM!!!!!!!!!
rs.Close
Set rs = Nothing


ADO is still validating the provider information even though it is
disconnected. If you check rs("One").Properties("BASETABLENAME") you'll see
that it's blank. On a column that really came from a table, this wouldn't be
blank and ADO would be OK with the update. All I'd like to do is strip away
all that provider information so it really behaves like a local-only record
set.

Thanks,
Mike




.

Relevant Pages

  • Re: DAO to ADO Recordset Options
    ... DAO was really fast when dealing with Access data (faster than ADO) but was ... Recordset. ... For client-sided cursors, there is only Static cursor type regardless as ... determines how often data is fetched from the server. ...
    (microsoft.public.data.ado)
  • Pessimistic locking with approles.
    ... It now uses ADO, server side cursors, application roles, ... say you have an ADO recordset open on a table Employee ... (Pessimistic locking requires a server side cursor.) ...
    (microsoft.public.sqlserver.security)
  • Re: ADO Data Control Concurrency Problem
    ... >> Editing of row in table is done with the help of ADO Data Control. ... Cursor location does not bare on whether the ... Client or Server memory. ... reflected in the open recordset. ...
    (microsoft.public.vb.general.discussion)
  • Re: Slow Stored Procedure when run via ado, fast from query analyzer
    ... > about 2 seconds from query analyzer, and takes 50+ seconds run from ADO ... If a Recordset - have you checked to see CursorLocation, CursorType, ... Server vs Client, it should be the same ballpark in magnitude. ...
    (microsoft.public.data.ado)
  • RE: TransferText from a table with a selection-parameter
    ... > temporary table where I store the recordset inside because there may be ... Each of your users can use separate temp tables in your SQL Server database. ...
    (microsoft.public.access.modulesdaovba)