Figure 3   New Recordset Functions in ADO 2.0

Feature Area Description
CompareBookmark Browsing Lets you compare bookmarks for any kind of data by acting as a proxy against the underlying OLE DB provider.
Find/Sort Browsing Adds search and sorting capability to a recordset.
Hierarchical Cursor Browsing Allows you to design complex and nested recordsets by merging and joining data more easily.
GetString Persistence Flattens out the recordset to a string.
Save/Load Persistence Lets you serialize to disk and restore the content of a recordset.
Append Field Data Management Allows you to use a recordset as a generic data buffer even without a DB connection.
Asynchronous Fetch Data Management Lets you fetch a recordset asynchronously. You're notified via events when the operation has finished.
ActiveCommand Information Returns the text of the command that originated the recordset.


Figure 4   Demonstrating ADO 2.0 in Visual Basic 6.0

Option Explicit

Private Sub ADOFun()
    Dim vBookmark As Variant
    Dim sql As String
    Dim RS As New ADODB.Recordset

    sql = "select * from authors"
    RS.CursorLocation = adUseClient
    RS.Open sql, "Pubs", , , adCmdText
    If RS.BOF Then Exit Sub

    vBookmark = RS.Bookmark
    Open "c:\demo.rst" For Output As #1
    Write #1, "The following command: " & _
        RS.ActiveCommand.CommandText & vbCrLf & _
        "produced:" & vbCrLf & vbCrLf
    Write #1, RS.GetString
    Close #1
    
    RS.Bookmark = vBookmark
    MsgBox "The first 10 records are:" & vbCrLf & _
        RS.GetString(, 10)
End Sub

Figure 5   ADO 2.0 Events

Event Name Object Description
BeginTransComplete, CommitTransComplete, RollbackTransComplete Connection These events are fired after the respective method with the same name minus the Complete suffix.
WillConnect, ConnectComplete, Disconnect Connection These events inform when a connection is about to set up, sets up successfully, when it gets closed.
EndOfRecordset Connection Raised when the user attempts to pass the last row in a recordset.
ExecuteComplete Connection Indicates that the execution of a command just finished. The parameters include the final recordset and the rows affected.
WillExecute Connection An Execute command is about to be run from a Connection or Recordset object. The event is also caused by the Recordset.Open method.
InfoMessage Connection Returns additional information from the provider for each successful connection operation.
WillChangeField, FieldChangeComplete Recordset Raised when the value of one or more fields is about to change or actually changed. These events are triggered by recordset operations like Value and Update.
WillChangeRecord, RecordChangeComplete Recordset Raised when the value of one or more rows is about to change or actually changed. These events are triggered by recordset operations like AddNew, Delete, Update, and UpdateBatch.
WillChangeRecordset, RecordsetChangeComplete Recordset Raised when a recordset is about to change or actually changed. These events are triggered by recordset operations like Requery, Resync, Filter, and Open.
WillMove, MoveComplete Recordset Raised when the current position in a recordset is going to be moved or was actually moved.
FetchComplete, FetchProgress Recordset Raised when a possibly lengthy fetch operation finished or as long as it completes.


Figure 7   Demonstrating Data Shaping

Private Sub cmdDataShaping_Click()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rsChapter As Variant
    Dim cmd, cmd1, cmd2, cmd3 As String

    cnn.Provider = "MSDataShape"
    cnn.Open "Biblio"

    rst.CursorLocation = adUseClient
    rst.StayInSync = False
    cmd1 = "SHAPE {select * from authors}" 
    cmd2 = "APPEND ({select * from [title author]} "
    cmd3 = "AS chapter RELATE au_id TO au_id)"
    cmd = cmd1 & cmd2 & cmd3   

    rst.Open cmd, cnn

    While Not rst.EOF
        Debug.Print rst("author") 
        Set rsChapter = rst("chapter").Value

        While Not rsChapter.EOF
            Debug.Print rsChapter(0) 
            rsChapter.MoveNext
        Wend
        rst.MoveNext
     Wend

     rst.Close
End Sub