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