You are currently offline, waiting for your internet to reconnect

How To Create ADO Disconnected Recordsets in VBA/C++/Java

This article was previously published under Q184397
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article illustrates one technique for creating an ActiveX Data Objects(ADO) disconnected recordset using Microsoft Visual Basic for Applications (VBA), Microsoft Visual C++, and Microsoft Visual J++. There are three ways to generate a disconnected ADO recordset, and this article references other articles that discuss them in detail.
A disconnected recordset is one of the powerful features of ADO wherein theconnection is removed from a populated recordset. This recordset can bemanipulated and again connected to the database for updating. Remote DataServices (RDS) uses this feature to send recordsets through either HTTP orDistributed Component Object Model (DCOM) protocols to a remote computer,however, you are not limited to using Remote Data Service (RDS) to generatea disconnected recordset. The three techniques for generating disconnectedrecordsets follow.
  • Using the Remote Data Service object model.
    RDS client side objects can be used to obtain a disconnected recordset, generated by a business object on a server computer running Internet Information Server. That business object is either one you create or the default RDS business object, the RDSServer DataFactory. Any recordset returned by RDS Server components is inherently disconnected. Techniques for using RDS client side components to return a disconnected recordset are discussed in the following Microsoft Knowledge Base article:
    183294 INFO: Techniques for Returning a Recordset via RDS
  • Using ADO to invoke the Remote Data Service, and return a disconnected recordset.
    The only difference is instead of instantiating an RDS client object to obtain the recordset, you use a connection string with the ADO Connection object to invoke server side RDS components to return the disconnected recordset. This technique is discussed in the following Microsoft Knowledge Base article:
    183628 How To Using the RDS DataFactory via Standard ADO Open Method
  • Manipulating ADO directly to disconnect a recordset without using either RDS Server or Client side components.
This technique is demonstrated below and is accomplished by setting the ActiveConnection property.

One of the primary requisites for a recordset to become a disconnected recordset is that it should use client side cursors. That is, the CursorLocation should be initialized to 'adUseClient'.

In Visual Basic, disconnecting a recordset can be done by setting the ActiveConnection property to 'Nothing'. With C++ and Java, you would accomplish the same thing by setting the ActiveConnection to NULL.

Here are some snippets of code that demonstrate how this may be done in the different languages:

Disconnected Recordsets Through Visual Basic Application

Dim Conn As ADODB.ConnectionDim Rs As ADODB.Recordset' Create instance of connection object and then open the' connection.Set Conn = New ADODB.ConnectionConn.Open "DSN=SQLServer", "sa", ""' Create instance of recordset object and open the' recordset object against a table.Set Rs = New ADODB.Recordset' Setting the cursor location to client side is important' to get a disconnected recordset.Rs.CursorLocation = adUseClientRs.Open "Select * from Table1", _         Conn, _         ADODB.adOpenForwardOnly, _         ADODB.adLockBatchOptimistic' Disconnect the recordset.Set Rs.ActiveConnection = Nothing' Get the value of one of the fields from the recordset' after disconnection.Dim vv = Rs.Fields(0).ValueMsgBox vConn.Close' Get the value of one of the fields from the recordset' after closing the connection to ensure that you have a' disconnected recordset.v = Rs.Fields(0).ValueMsgBox (v)' Now edit the value and save it.Rs.Fields("au_lname").Value = "NewValue"' Now reopen the connection and attach it to the recordset.  UpdateSet Conn = New ADODB.ConnectionConn.Open "DSN=DBSql", "sa", ""Rs.ActiveConnection = ConnRs.UpdateBatchRs.CloseConn.CloseSet Rs = NothingSet Conn = Nothing				

Disconnected Recordsets through Visual C++

NOTE: This code sample uses the #import within C++.
try{   _RecordsetPtr spRS(__uuidof(Recordset));   _ConnectionPtr spConn(__uuidof(Connection));   // Connect and get recordset.   spConn->Open(OLESTR("dsn=SQLServer"),OLESTR("sa"),                OLESTR(""), -1);   spRS->CursorLocation = adUseClient;   spRS->Open(OLESTR("select * from Table1"),     spConn.GetInterfacePtr(),       adOpenForwardOnly, adLockBatchOptimistic, -1);   // Disassociate the connection from the recordset.   spRS->PutRefActiveConnection(NULL);   // Change the value.   spRS->Fields->Item[0L]->Value = OLESTR("NewValue");   // Re-connect.   spRS->PutRefActiveConnection(spConn);   // Send updates.   spRS->UpdateBatch(adAffectAll);   spRS->Close();   spConn->Close();}catch( _com_error e){   // Do Exception handling.}				
In the preceding code instead of using this:
You can use the following code:
var.vt = VT_UNKNOWN;var.punkVal = NULL;spRS->PutActiveConnection(var);				
var.vt = VT_DISPATCH;var.pdispVal = NULL;spRS->PutActiveConnection(var);				

Disconnected Recordset through Java

This technique uses ADO class wrappers generated by the Java Typelib Wizard.
try{   Conn1   = new msado15.Connection();   Rs1     = new msado15.Recordset();   Conn1.Open( "DSN=SQLServer", "sa", "", -1 );   // Open recordset using the connection above.   Rs1.putCursorLocation(CursorLocationEnum.adUseClient);   Rs1.putActiveConnection( Conn1 );   Variant vv = new Variant("select * from table1");   Variant varSkip = new Variant();   varSkip.noParam();   Rs1.Open(vv,varSkip, CursorTypeEnum.adOpenForwardOnly,        LockTypeEnum.adLockBatchOptimistic,-1);   String str;   Variant var;   Variant v0 = new Variant(0);   var = Rs1.getFields().getItem(v0).getValue();   // Disconnect recordset.   Rs1.putActiveConnection(null);   // Update value in recordset.   var.putString("Smith");   Rs1.getFields().getItem(v0).putValue(var);   // Re-connect.   Rs1.putActiveConnection(Conn1);   // Transfer updated records.   Rs1.UpdateBatch(AffectEnum.adAffectAll);   Rs1.Close();   Conn1.Close();}catch ( e){ // Error proc}				
For additional information, please see the following article in theMicrosoft Knowledge Base:
220152 Sample: ADOVC1 Simple ADO/VC++ Application

Article ID: 184397 - Last Review: 06/29/2004 21:28:55 - Revision: 1.1

Microsoft ActiveX Data Objects 1.5, Microsoft ActiveX Data Objects 2.0, Microsoft ActiveX Data Objects 2.1, Microsoft ActiveX Data Objects 2.5, Microsoft ActiveX Data Objects 2.6, Microsoft ActiveX Data Objects 2.7

  • kbcode kbdatabase kbhowto KB184397