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. For more info read MSDN article about Fields Collection
- Properties
- Contains all the Property objects for a specific instance of an object. For more info read MSDN article about Properties Collection
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]"