HOWTO: Getting ADO Disconnected Recordsets in VBA/C++/Java

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1

SUMMARY

This article illustrates one technique for creating an ActiveX Data Objects (ADO) disconnected recordset using Visual Basic for Applications (VBA), Visual C++ and Visual J++. There are three ways to generate a disconnected ADO recordset, and this article references other articles that discuss them in detail.

MORE INFORMATION

A disconnected recordset is one of the powerful features of ADO wherein the connection is removed from a populated recordset. This recordset can be manipulated and again connected to the database for updating. Remote Data Services (RDS) uses this feature to send recordsets through either HTTP or Distributed Component Object Model (DCOM) protocols to a remote computer, however, you are not limited to using Remote Data Service (RDS) to generate a disconnected recordset. The three techniques for generating disconnected recordsets 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:

Q183294 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:

Q183628 HOWTO: 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.Connection
Dim Rs As ADODB.Recordset

' Create instance of connection object and then open the
' connection.
Set Conn = New ADODB.Connection
Conn.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 = adUseClient
Rs.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 v
v = Rs.Fields(0).Value
MsgBox v

Conn.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).Value
MsgBox (v)

' Now edit the value and save it.
Rs.Fields("au_lname").Value = "NewValue"

' Now reopen the connection and attach it to the recordset.  Update
Set Conn = New ADODB.Connection
Conn.Open "DSN=DBSql", "sa", ""
Rs.ActiveConnection = Conn
Rs.UpdateBatch

Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing 

Disconnected Recordsets through Visual C++

try
{
   ADODB::_RecordsetPtr spRS(__uuidof(ADODB::Recordset));
   ADODB::_ConnectionPtr spConn(__uuidof(ADODB::Connection));

   // Connect and get recordset.
   spConn->Open(OLESTR("dsn=SQLServer"),OLESTR("sa"),
                OLESTR(""), -1);

   spRS->CursorLocation = ADODB::adUseClient;
   spRS->Open(OLESTR("select * from Table1"),
     spConn.GetInterfacePtr(),
       ADODB::adOpenForwardOnly, ADODB::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(ADODB::adAffectAll);

   spRS->Close();
   spConn->Close();

}
catch( _com_error e)
{
   // Do Exception handling.
} 

spRS->PutRefActiveConnection(NULL); 

var.vt = VT_UNKNOWN;
var.punkVal = NULL;
spRS->PutActiveConnection(var); 

-or-

var.vt = VT_DISPATCH;
var.pdispVal = NULL;
spRS->PutActiveConnection(var); 

Disconnected Recordset through Java

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 (com.ms.com.ComFailException e)
{
 // Error proc
} 

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

Q220152 Sample: ADOVC1 Simple ADO/VC++ Application

Additional query words:

Keywords : kbADO kbDatabase kbVBp kbVC kbVJ kbGrpVCDB kbGrpMDAC kbDSupport
Version : WINDOWS:1.5,2.0,2.1
Platform : WINDOWS
Issue type : kbhowto