Recordset object

The Recordset object allows you to work with the records returned from a query on a table.

Creating a Recordset object

Use the new keyword to create a new Recordset object.

{Create a new recordset.}
recordset = new ADODB.Recordset();

When using Distributed COM (DCOM), create a new Recordset object with the COM_CreateObject() function. In the following example, a new Recordset object is created on the computer “SystemServer”.

{Create a new recordset object.}
recordset = COM_CreateObject("ADODB.Recordset", "SystemServer");

Creating a Recordset object doesn’t create a recordset for a table. You must also use the Open method to add records from a table to a recordset.

Opening a recordset

Use the Open method to add records to a recordset. The Open method allows you to use a SQL query to select records from tables in a database.

To specify how you work with records in the database, use values from the CursorTypeEnum enumeration. To specify how the database is locked, use values from the LockTypeEnum enumeration.

{Open a recordset on the BuyerInfo table.}
recordset.Open("SELECT * FROM BuyerInfo", connection, ADODB.adOpenDynamic, ADODB.adLockOptimistic);

In the previous example, the recordset is opened dynamically, which allows you to move both forward and backward through the records. Also, the recordset is locked record by record.

Moving through a recordset

Several methods are available to move through the records in a Recordset object, such as the Move method, the MoveFirst method, the MoveLast method, the MoveNext method, and the MovePrevious method. Use the Move method to move forward a specified number of records.

{Move forward five records.}
recordset.Move(5);

Use the MoveFirst method to move to the first record in the recordset.

{Move to the first record.}
recordset.MoveFirst();

Use the BOF property to find out if you are at the beginning of a recordset.

Use the MoveLast method to move to the last record in a recordset.

{Move to the last record.}
recordset.MoveLast();

Use the EOF property to find out if you are at the end of a recordset.

Use the MoveNext method to move to the next record.

{Move to the next record.}
recordset.MoveNext();

Use the MovePrevious method to move to the previous record in the recordset.

{Move to the previous record.}
recordset.MovePrevious();

Adding a record

To add a new record to a recordset, you must call the AddNew method and set the values of the fields in the record. Then you must call the Update method to add the record to the recordset.

{Add a new record to the BuyerInfo table.}
recordset.AddNew();
recordset.Fields.Item[0].Value = 'Buyer ID';
recordset.Fields.Item[1].Value = 'Buyer First Name';
recordset.Fields.Item[2].Value = 'Buyer Last Name';
recordset.Fields.Item[3].Value = Phone;
recordset.Update();

Updating a record

To update a record in a recordset, you must move to the record and set the values for the fields that you want to update in the record. Then you must call the Update method to save the changes.

{Update the last record in the BuyerInfo table.}
recordset.MoveLast();
recordset.Fields.Item[1].Value = 'Buyer First Name';
recordset.Update();

Deleting a record

To delete a record in a recordset, you must move to the record you want to delete and call the Delete method. Then you must call the Update method to update the recordset.

{Delete the last record in the BuyerInfo table.}
recordset.MoveLast();
recordset.Delete();
recordset.Update();

Closing a recordset

Use the Close method to close a recordset.

{Close a recordset.}
recordset.Close();