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.



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.


.AddNew FieldLists,Values

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.




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.


You can find similar Excel Questions and Answer hereunder

1) How can i filter multiple columns simultaneously in Excel

2) How can I have text autocomplete by typing in a short code for the text?

3) Here some explanations about xml and how to use xml in VBA

4) Formula converts date to quarter and year in Excel

5) What is activeX data object in Excel. How to use it? Here the basic explanations

6) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

7) Here some explanations about the XML DOM node in VBA, DOM stands for Document Object Model

8) How can I enter information in multiple cells simultaneously?

9) How to use goal seek in Excel. Some good introduction to goal seeking function

10) Highlight row of selected cell in Excel


Here the previous and next chapter