The Recordset object provides a method of accessing our data. This recordset object depends opon the understanding of the Connection object.
A D V E R T I S E M E N T
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
