ADO RecordSet Populate

Populate the recordset

The ADO Recordset object is used to contain the set of data extracted from a database and is composed of records (rows) and fields (columns).

The Recordset object is the heart of ADO. Via this object, we can select desired data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.

RecordSet.Open(Source, Connection, CursorType, LockType, Options)

The Open method is called on a Recordset object to open a cursor which gives you access to the records contained in the base table, the results from a query (used in the examples of this tutorial), or a previously saved Recordset.
There are five optional parameters.

Source

This optional parameter is a variant that can be any one of the following data sources:

• SQL query string (used in the examples of this tutorial)
• table name
• Others: More details can be found in section → External Links - ADO

Connection

This optional parameter is a valid Connection object (used in the examples of this tutorial) or a connection string that defines the connection.

CursorType

This optional parameter specifies the type of cursor to use when you open a Recordset object.

Possible values for the CursorTypeEnum enumeration are:

Constant	Value	Description
adOpenUnspecified	-1	Cursor type not specified
adOpenForwardOnly	0	(Default) A forward scrolling only, static cursor where changes made by other users are not visible
adOpenKeyset	1	A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
adOpenDynamic	2	A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
adOpenStatic	3	A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records

LockType

This optional parameter indicates the type of lock in effect on a Recordset.

Possible values for the LockTypeEnum enumeration are:

Constant	Value	Description
adLockBatchOptimistic	4	Multiple users can modify the data and the changes are cached until BatchUpdate is called
adLockOptimistic	3	Multiple users can modify the data which is not locked until Update is called
adLockPessimistic	2	The provider locks each record before and after you edit, and prevents other users from modifying the data
adLockReadOnly	1	(Default) Read-only data
adLockUnspecified	-1	Lock type unknown

Options

This optional parameter tells the provider how to evaluate the Source parameter when it contains something other than a Command object. The appropriate use of this option can speed up performance since ADO will not have to determine the type of the data source.

Possible values for the CommandTypeEnum enumeration are:

Constant	Value	Description
adCmdUnspecified	-1	(Default) does not specify how to evaluate
adCmdText	1	Evaluates as a textual definition of a command or stored procedure call
adCmdTable	2	Have the provider generate an SQL query and return all rows from the specified table
adCmdStoredProc	4	Evaluate as a stored procedure
adCmdUnknown	8	The type of the CommandText parameter is unknown. ADO will make several attempts to interpret the CommandText: adCmdText then adCmdStoredProc then adCmdTable
adCmdFile	256	Evaluate as a previously persisted file
adCmdTableDirect	512	Return all rows from the specified table

Recordset properties

List of useful RecordSet properties. A full list of properties, collections and methods can be found here

BOF

Returns a Boolean value indicating if the current record position is before the first record

EOF

Returns a Boolean value indicating if the current record position is after the last record

Filter

Sets or returns a variant value that is either a string, array of bookmarks, or a FilterGroupEnum value used to filter data. You also use this property to turn an existing Filter off

MaxRecords

Sets or returns a long value that specifies the maximum number of records that can be returned to a Recordset object as the result of a query

RecordCount

Returns a long value that is the count of how many records are in a Recordset object

Sort

Sets or returns a string value that is a comma-delineated list of the names of which fields in the Recordset to sort. After each name, you can optionally add a blank space and the keywords ASC or DESC to designate the sort direction

Recordset collections

List of useful RecordSet collections. A full list of properties, collections and methods can be found here

Fields

A collection containing all of the Field objects associated with a Recordset object

Recordset methods

A list of useful RecordSet methods can be found in section → Search/process the fetched data.

Select Excel data

Excel data may be contained in the workbook in one of the following:

• An entire worksheet
• A named range of cells on a worksheet
• An unnamed range of cells on a worksheet

Specify a Worksheet
To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:

$sSQL = "SELECT * FROM [Sheet1$]"

Specify a Named Range
To specify a named range of cells as your recordsource, simply use the defined name. For example:

$sSQL = "SELECT * FROM MyRange"

Specify an Unnamed Range
To specify an unnamed range of cells as the recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:

$sSQL = "SELECT * FROM [Sheet1$A1:B10]"