ADO Recordset Object

ActiveX data Object(ADO) Recordset Objects represents the entire set of records from a base table or the results of an executed command. At any time, Recordset object refers to only a single record within the set as the current record.

Use the Recordset object to issue Structured Query Language (SQL) SELECT commands in folders.

For example:

Dim Conn as New ADODB.Connection
Dim Rs as New ADODB.Recordset
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open RootFolderURL

Set Rs.ActiveConnection = Conn
"select ""DAV:displayname"" " _
& "from scope('shallow traversal of ""URL""')" _
& "Where ""DAV:ishidden"" = False"

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

ActiveX Data Object(ADO) Recordset objects is used to manipulate data from a provider. When we use ADO, we manipulate data almost entirely using Recordset objects. All Recordset objects consist of records (rows) and columns (fields). Depending on the functional support by the provider, some Recordset methods or properties may not be available.

When we first open a Recordset, the current record pointer will point to the First record and the BOF and EOF properties are False. If there are no records are present, the BOF and EOF property are True.

There are two type of Updating that will supported by (ADO) Recordset objects :

1: Immediate updating

2: Batch updating

1: Immediate updating

When you call the Update method all changes are written immediately to the database .

2: Batch updating

The provider will cache the multiple changes and then send them to the record in the database with the UpdateBatch method.

What is Curser?

Curser is a database element that updatability of data ,controls record navigation, and the visibility of changes made to therecords in the database by other users.

There are four different cursor types defined in ADO:

1: Dynamic cursor

2: Keyset cursor

3: Static cursor

4: Forward-only cursor

1: Dynamic cursor

Allows us to additions ,view , changes, and deletions by the other users; it also allows all types of movement through the Recordset that does not rely on bookmarks; and allows bookmarks if the provider supports them.

2: Keyset cursor

Behaves like a dynamic cursor, except that it prevents us from seeing the records that the other users add, and prevents access to the records that other users delete. Data changes by other users will still be visible. It always supports bookmarks and therefore allows all types of movement through the Recordset.

3: Static cursor

Provides a static copy of a set of the records for us to use to find the data or generates that reports;it always allows bookmarks and therefore allows all types of movement through the Recordset. Additions,deletions or changes by other users will not be visible. This is the only type of cursor allowed when you open a client-side ADO Recordset object.

4: Forward-only cursor

Allows us to only scroll forward through the Recordset in the database. Changes,Additions or deletions by other users will not be visible. This improves performance in situations where you need to make only a single pass through a Recordset.

Properties of ADO Recordset Object.

Property	Description
AbsolutePage	Sets or returns a value that specifies the page number in the Recordset object
AbsolutePosition	Sets or returns a value that specifies the ordinal position of the current record in the Recordset object
ActiveCommand	Returns the Command object associated with the Recordset
ActiveConnection	Sets or returns a definition for a connection if the connection is closed, or the current Connection object if the connection is open
BOF	Returns true if the current record position is before the first record, otherwise false
Bookmark	Sets or returns a bookmark. The bookmark saves the position of the current record
CacheSize	Sets or returns the number of records that can be cached
CursorLocation	Sets or returns the location of the cursor service
CursorType	Sets or returns the cursor type of a Recordset object
DataMember	Sets or returns the name of the data member that will be retrieved from the object referenced by the DataSource property
DataSource	Specifies an object containing data to be represented as a Recordset object
EditMode	Returns the editing status of the current record
EOF	Returns true if the current record position is after the last record, otherwise false
Filter	Sets or returns a filter for the data in a Recordset object
Index	Sets or returns the name of the current index for a Recordset object
LockType	Sets or returns a value that specifies the type of locking when editing a record in a Recordset
MarshalOptions	Sets or returns a value that specifies which records are to be returned to the server
MaxRecords	Sets or returns the maximum number of records to return to a Recordset object from a query
PageCount	Returns the number of pages with data in a Recordset object
PageSize	Sets or returns the maximum number of records allowed on a single page of a Recordset object
RecordCount	Returns the number of records in a Recordset object
Sort	Sets or returns the field names in the Recordset to sort on
Source	Sets a string value or a Command object reference, or returns a String value that indicates the data source of the Recordset object
State	Returns a value that describes if the Recordset object is open, closed, connecting, executing or retrieving data
Status	Returns the status of the current record with regard to batch updates or other bulk operations
StayInSync	Sets or returns whether the reference to the child records will change when the parent record position changes

Methods of ADO Recordset Object.

Methods	Description
AddNew	Creates a new record
Cancel	Cancels an execution
CancelBatch	Cancels a batch update
CancelUpdate	Cancels changes made to a record of a Recordset object
Clone	Creates a duplicate of an existing Recordset
Close	Closes a Recordset
CompareBookmarks	Compares two bookmarks
Delete	Deletes a record or a group of records
Find	Searches for a record in a Recordset that satisfies a specified criteria
GetRows	Copies multiple records from a Recordset object into a two-dimensional array
GetString	Returns a Recordset as a string
Move	Moves the record pointer in a Recordset object
MoveFirst	Moves the record pointer to the first record
MoveLast	Moves the record pointer to the last record
MoveNext	Moves the record pointer to the next record
MovePrevious	Moves the record pointer to the previous record
NextRecordset	Clears the current Recordset object and returns the next Recordset object by looping through a series of commands
Open	Opens a database element that gives you access to records in a table, the results of a query, or to a saved Recordset
Requery	Updates the data in a Recordset by re-executing the query that made the original Recordset
Resync	Refreshes the data in the current Recordset from the original database
Save	Saves a Recordset object to a file or a Stream object
Seek	Searches the index of a Recordset to find a record that matches the specified values
Supports	Returns a boolean value that defines whether or not a Recordset object supports a specific type of functionality
Update	Saves all changes made to a single record in a Recordset object
UpdateBatch	Saves all changes in a Recordset to the database. Used when working in batch update mode

Events:

Events	Description
EndOfRecordset	Triggered when you try to move to a record after the last record
FetchComplete	Triggered after all records in an asynchronous operation have been fetched
FetchProgress	Triggered periodically in an asynchronous operation, to state how many more records that have been fetched
FieldChangeComplete	Triggered after the value of a Field object change
MoveComplete	Triggered after the current position in the Recordset has changed
RecordChangeComplete	Triggered after a record has changed
RecordsetChangeComplete	Triggered after the Recordset has changed
WillChangeField	Triggered before the value of a Field object change
WillChangeRecord	Triggered before a record change
WillChangeRecordset	Triggered before a Recordset change
WillMove	Triggered before the current position in the Recordset changes