Vba recordset in Excel
A Recordset is the one which sores the data retrived from a SQL query.
It represents the entire set of records from a base table or the results of an executed command.
Recordset are almost always used along with ADO.
A recordset object consists of "Records" also known as Rows and "Fields" or Columns.
There are various methods and properties of Recordset object, the availability of which are dependent on
the provider used.
Some of the most common methods and properties of Recordset object is discussed below.
Update method
This method saves any changes or modifications made to the current row of the recordset.
without Update method, the recordset will not be updated.
Syntax:
AddNew Method
This method is used to create and initialize new record in a recordset.
The records added through AddNew method acts as the current record of the recordset.
The AddNew method should be used along with the Update method.
Syntax
Close method
The Close method is of general type and can be used along with Connection,a record or recordset.
This method is used to close any active recordset objects.
Note that, closing a recordset does not delete it and is always accessible again when opened.
Syntax
EOF and BOF
EOF and BOF properties are used while looping through a recordset.
EOF indicates that the current record position is after the last record in a recordset.
While BOF indicates that the current recod position is before the first record in a recordset.
These properties are used to determine if a recordset object contains records or its limit is reached.
The EOF return TRUE if the current record position is after the last record.
Similarly, The BOF returns TRUE if the current record position is before the first record.
RecordCount Property
As the name indicates, this property returns the number of records in a recordset.
This property returns a long datatype.
The RecordCount property can be used in a recordset that is open.
Using this property in a closed Recordset object results in error.