Creatable Recordset Objects

Often, a developer finds a need for a place to temporarily store some data, or wants some data to act like it came from a server so it can participate in data binding in a user interface.

ADO (in conjunction with the Cursor Service for OLE DB) enables the developer to build an empty Recordset object by specifying column information and calling Open. The following code demonstrates this:

Sub ADOCreateRecordset()

   Dim rst As New ADODB.Recordset

   rst.CursorLocation = adUseClient

   ' Add Some Fields

   rst.Fields.Append "dbkey", adInteger

   rst.Fields.Append "field1", adVarChar, 40, adFldIsNullable

   rst.Fields.Append "field2", adDate

   ' Create the Recordset

   rst.Open , , adOpenStatic, adLockBatchOptimistic

   ' Add Some Rows

   rst.AddNew Array("dbkey", "field1", "field2"), _

      Array(1, "string1", Date)

   rst.AddNew Array("dbkey", "field1", "field2"), _

      Array(2, "string2", #1/6/1992#)

   ' Look at the values -

   ' a value of 1 for status column = newly record

   rst.MoveFirst

   Debug.Print "Status", "dbkey", "field1", "field2"

   Do Until rst.EOF

      Debug.Print rst.Status, rst!dbkey, rst!field1, rst!field2

      rst.MoveNext

   Loop

   ' Commit the rows without ActiveConnection

   ' set resets the status bits

   rst.UpdateBatch adAffectAll

   ' Change the first of the two rows

   rst.MoveFirst

   rst!field1 = "changed"

   ' Now look at the status, first row shows 2 (modified row),

   ' second shows 8 (no modifications)

   ' Also note that the OriginalValue property shows the value

   ' before the modification

   rst.MoveFirst

   Do Until rst.EOF

      Debug.Print

      Debug.Print rst.Status, rst!dbkey, rst!field1, rst!field2

      Debug.Print , rst!dbkey.OriginalValue, _

         rst!field1.OriginalValue, rst!field2.OriginalValue

      rst.MoveNext

   Loop

End Sub

Another feature of a creatable recordset is that pending operations can be committed to the recordset. Any time UpdateBatch is called on a client cursor that has no ActiveConnection set, the changes in the affected row (controlled by the AffectedRows parameter) will be committed to the buffer and the Status flags will be reset. The same applies to CancelBatch, except the changes in the buffer will be reverted and the flag will be reset.