Entry

Is it possible to disconnect the RecordSet and use the Rs.Update method later in the code? Or do I need to reconnect.

Jul 17th, 2000 05:44
unknown unknown, Jacob Sulzbach

Yes it is possible to disconnect a recordset and use the Update method 
later in the code.  And you can perform two types of updates, single 
record and batch types, depending upon the locktype setting you choose, 
though batch updating is far preferable from every angle for most apps.
If you are going to use disconnected recordsets on the client, you 
should make sure you are in an ActiveX-enabled environment.  In browsers 
this means Internet Explorer 4x & 5x, and EXCLUDES Netscape in all 
versions.  Without an ActiveX-enabled environment like Internet Explorer 
you cannot use ADO on the client.
I have a link on an article in an RDS tutorial (RDS uses disconnected 
recordsets exclusively) that gives a general overview of the important 
settings for disconnected recordsets in ADO – it is a background article 
and does not deal with RDS specifically, the focus is disconnected 
recordsets in general.
http://www.rdsdeveloper.com/ASPTutorialPart1DissRec.html
You may also want to read a little on ADO on the client:
http://www.rdsdeveloper.com/ASPTutorialPart1ADOASP.html 
And as for your question about updates, you really should use the 
LockBatchOptimistic locktype where you can do the following while on the 
client, in client-side VBScript:
Sub AddRecord()
  objRS.AddNew
  objRS(“MyField”) = strMyField
  objRS(“MyOtherField”) = strMyOtherField
  objRS.Update
End Sub
The above code will add the record to the recordset on the client.  But 
it remains disconnected and not a part of your data source on the server 
until you reconnect and upload your changes, like so:
‘this is a client-side VBScript example, does not use the Server object
Sub UpdateSource()
  Set objConn = CreateObject(“ADODB.Connection”)
  strConnect = ‘insert your connection string here
  objConn.Open strConnect
  ‘the following line reconnects the recordset 
  Set objRS.ActiveConnection = objConn
  ‘the following line updates the data source
  objRS.UpdateBatch
  Set objRS.ActiveConnection = Nothing
  If objConn.State = adStateOpen Then objConn.Close
  Set objConn = Nothing
End Sub
Since the recordset was never destroyed after creating it – it could 
have been merely disconnected and “live” or persisted to a file or 
object and reopened later – its identity remains intact (its SQL 
definition) and the upload of your changes will be effected by 
reconnecting and using the UpdateBatch method on the server.