Using the Recordset Object

The Recordset object allows your application to access data returned from a SQL query. This query can be created by the application, or it can reside on the server as a stored procedure. Using the Recordset object, you can navigate the records that have been returned, or edit their values.

can navigate the records that have been returned, or edit their values.

Recordset Object Properties

The following table describes properties of the Recordset object that are commonly used to create a Recordset. For a complete listing of Recordset object properties, read the article "Recordset Object (ADO)" in ADO Help.

Property	Description
ActiveCommand	Returns the active command for the recordset.
ActiveConnection	Sets or returns the active connection for the recordset.
CursorLocation	Sets or returns the location of the cursor. The default is adUseServer.
CursorType	Sets or returns the cursor type. The default is adOpenForward.
LockType	Sets or returns the type of record locking. The default is adLockReadOnly.
MaxRecords	Sets or returns the maximum number of records to return.
PersistFormat	Determines the format in which the recordset data is saved when calling the Save method.
RecordCount	Returns the number of records in the recordset.
State	Returns the current state of the recordset.

Recordset Object Methods

The following table describes methods of the Recordset object that are commonly used to create a recordset. For a complete listing of Recordset object methods, read the article "Recordset Object (ADO)" in ADO Help.

Method	Description
Open	Executes a SQL command and opens a cursor.
Close	Closes the recordset.
Requery	Re-executes a SQL command and rebuilds the recordset.
Resync	Refreshes cached records in a recordset.
Save	Saves an open recordset to a file that can be re-opened later.

Creating a Recordset

You can build a Recordset object based on an active connection to a data source. This limits the number of connections and can reduce the amount of client and server resources used by your application. Depending on the needs of the recordset, you may need to build an explicit Connection object or Command object first. If you do not explicitly use a Connection object or a Command object, a stand-alone recordset is automatically created.

The functionality of the recordset you create is determined by the values specified for the CursorLocation and CursorType properties.

Using a Connection Object and a Command Object

You can create a new recordset from an existing Command object. Open the connection and create the Command object. Then use the Command object's Execute method to build the recordset.

To see sample code that uses an existing Command object to build a recordset, click on the icon below.

Using the Open Method

You can create a new recordset directly from an existing active connection. Open the connection normally, and pass the Connection object to the recordset using the Open method.

To see sample code that creates a recordset from an existing Connection object, click on the icon below.

Using the Execute Method

You can also create a recordset using the Execute method of the Connection object. The Execute method does not support the same arguments as the Open method and therefore your recordset assumes the properties set originally on the Connection object. Using this technique, you are limited in the features that can be associated with the recordset.

To see sample code that creates a Recordset object against an existing active connection, click on the icon below.

Creating a Stand-Alone Recordset

When you create a stand-alone Recordset object, a Connection object does not have to already exist. A Connection object will be created automatically, but it cannot be accessed from Visual Basic. When the Open method is used, the required connection information must be passed to ADO.

The following example code shows how to create a Recordset object as a stand-alone object:

Dim rsStudents As Recordset
Set rsStudents = New Recordset
  
rsStudents.Open "SELECT StudentID FROM Students", _
                "Provider=SQLOLEDB;" & _
                "User ID=sa;" & _
                "Data Source=MSERIES1;Initial Catalog=StateUBookstore"