MS-Access / Getting Started

---

Working with ADO Recordsets

Recordsets in ADO offer many of the same capabilities and options as recordsets in DAO, but the terminology is somewhat different. Because you will most often use ADO with data stored in a server database such as SQL Server, the options for an ADO recordset are geared toward server-based data. For example, ADO uses the term cursor to refer to the set of rows returned by the server. Fundamentally, a cursor is a pointer to each row you need to work with in code. Depending on the options you choose (and the options supported by the particular database server), a cursor might also be read-only, updateable, or forward-only. A cursor might also be able to reflect changes made by other users of the database (a keyset or dynamic cursor), or it might present only a snapshot of the data (a static cursor).

To open an ADO recordset, you must use the Open method of a new ADO Recordset object. The syntax to use the Open method of a Recordset object is as follows:

RecordSetObject.Open [source], [connection],
  [cursortype], [locktype], [options]

RecordSetObject is a variable you have declared as a New ADO.Recordset. Source is a Command object, a string variable, or string literal containing the name of a table, the name of a view (the SQL Server term for a query), the name of a stored procedure, the name of a function that returns a table, or a valid SQL statement. A stored procedure might be a parameter query or a query that specifies the sorting of rows from a table or view. A function might also accept parameters. If you supply a Command object as the source, you do not need to supply a connection (you define the connection in the Command object). Otherwise, connection must be the name of a Connection object that points to the target database.

Table-3 describes the settings you can supply for cursortype, lockoptions, and options.

Table-3 RecordSetObject.Open Parameter Settings

Setting	Description
CursorType (Select one)
adOpenForwardOnly	Returns a read-only snapshot cursor (recordset) that you can move forward through only once. You can use the MoveNext method to access successive rows. If you do not supply a CursorType setting, adOpenForwardOnly is the default.
adOpenKeyset	Returns a Keyset cursor. This is roughly analogous to a DAO dynaset. If you are using ADO to open a recordset against a source in an Access .accdb file, you should use this option to obtain a recordset that behaves most like a DAO recordset. In this type of cursor, you will see changes to rows made by other users, but you will not see new rows added by other users after you have opened the cursor.
adOpenDynamic	Returns a dynamic cursor. This type of cursor lets you see not only changes made by other users, but also added rows. Note, however, that certain key properties you might depend on in a DAO recordset such as RecordCount might not exist or might always be zero.
adOpenStatic	Returns a read-only snapshot cursor. You won't be able to see changes made by other users after you've opened the cursor.
LockType (Select one)
adLockReadOnly	Provides no locks. The cursor is read-only. If you do not provide a lock setting, this is the default.
adLockPessimistic	Asks the target database to lock a row as soon as you place the row in an editable state by executing an Edit method.
adLockOptimistic	Asks the target database not to attempt to lock a row until you try to write it to the database with an Update method. This generates a run-time error in your code if another user has changed the row after you executed the Edit method. You should use this option when accessing rows in an Access .accdb file.
Options (You can combine one Cmd setting with one Async setting with a plus sign)
adCmdText	Indicates that source is an SQL statement.
adCmdTable	Indicates that source is a table name (or a query name in a desktop database). In DAO, this is analogous to opening a dynaset recordset on a table.
adCmdTableDirect	Indicates that source is a table name. This is analogous to a DAO dbOpenTable.
adCmdStoredProc	Indicates that source is a stored procedure. In DAO, this is analogous to opening a dynaset on a sorted query.
adAsyncFetch	After fetching the initial rows to populate the cursor, additional fetching occurs in the background. If you try to access a row that has not been fetched yet, your code will wait until the row is fetched.
adAsyncFetchNonBlocking	After fetching the initial rows to populate the cursor, additional fetching occurs in the background. If you try to access a row that has not been fetched yet, your code will receive an end-of-file indication.

For example, to declare a recordset for the tblFacilities table in the Hotel Reservation database (Hotel.accdb) and open the recordset as a table so you can use its indexes, enter the following:

Dim cnThisConnect As ADODB.Connection
Dim rcdFacilities As New ADODB.RecordSet
Dim rcdBooks As New ADODB.Recordset
Set cnThisConnect = CurrentProject.Connection
rcdFacilities.Index = "PrimaryKey"
rcdBooks.Open "tblFacilities", cnThisConnect, adOpenKeyset, _
    adLockOptimistic, adCmdTableDirect

Note that you must establish the index you want to use before you open the recordset. (If you want to try this in the Hotel Reservation database, Hotel.accdb, you'll need to add a reference to the Microsoft ActiveX Data Objects Library.)

To open the qryContactProducts query in the Conrad Systems Contacts database as a keyset, enter the following:

Dim cnThisConnect As ADODB.Connection
Dim rcdContactProducts As New ADODB.RecordSet
Set cnThisConnect = CurrentProject.Connection
rcdContactProducts.Open "qryContactProducts", _
  cnThisConnect, adOpenKeyset, adLockOptimistic, _
  adCmdTable

After you open a recordset, you can use one of the Move methods to move to a specific record. Use recordset.MoveFirst to move to the first row in the recordset. Other Move methods include MoveLast, MoveNext, and MovePrevious. If you want to search for a specific row in the recordset, use the Find method or set the recordset's Filter property. Unlike the Find methods in DAO, the Find method in ADO is limited to a single simple test on a column in the form "<column-name> <comparison> <comparison-value>". Note that to search for a Null value, you must say: "[SomeColumn] = Null", not "[SomeColumn] Is Null" as you would in DAO. Also, <comparison> can be only <, >, <=, >=, <>, =, or LIKE. Note that if you want to use the LIKE keyword, you can use either the ANSI wildcards "%" and "_" or the Access ACE/JET wildcards "*" and "?", but the wildcard can appear only at the end of the <comparison-value> string.

If you want to search for rows using a more complex filter, you must assign a string variable or an expression containing the criteria for finding the records you want to the Filter property of the recordset. This limits the rows in the recordset to only those that meet the filter criteria. The criteria string must be made of the simple comparisons that you can use with Find, but you can include multiple comparisons with the AND or OR Boolean operator.

For example, to find the first row in the qryContactProducts query's recordset whose Sold- Price field is greater than $200, enter the following:

rcdContactProducts.MoveFirst
rcdContactProducts.Find "SoldPrice > 200"
' EOF property will be true if nothing found
If Not rcdContactProducts.EOF Then
' Found a record!

To find all rows in qryContactProducts where the product was sold after November 1, 2010, and SoldPrice is greater than $200, enter the following:

rcdContactProducts.Filter = &
  "DateSold > #11/1/2010# AND SoldPrice > 200"
' EOF property will be true if filter produces no rows
If Not rcdODetails.EOF Then
' Found some rows!

To delete a row in a keyset, simply move to the row you want to delete and then use the Delete method. For example, to delete the first row in the qryContactProducts query's recordset that hasn't been invoiced yet (the Invoiced field is false), enter the following:

Dim cnThisConnect As ADODB.Connection
Dim rcdContactProducts As New ADODB.RecordSet
Set cnThisConnect = CurrentProject.Connection
rcdContactProducts.Open "qryContactProducts", _
  cnThisConnect, adOpenKeyset, adLockOptimistic, _
  adCmdTable
rcdContactProducts.MoveFirst
rcdContactProducts.Find "Invoiced = 0"
' Test the recordset EOF property for "not found"
If Not rcdContactProducts.EOF Then
  rcdContactProducts.Delete
End If

Note that in this example, if tblContactRelatedProducts includes related records, Access prevents the deletion. If you want to update rows in a recordset, move to the first row you want to update. You can refer to any of the updateable fields in the row by name to change their values. You can use the Update method on the recordset to explicitly save your changes before moving to another row. ADO automatically saves your changed row when you move to a new row. If you need to discard an update, you must use the CancelUpdate method of the recordset object.

For example, to increase by 10 percent the SoldPrice entry of the first row in the rcd- ContactProducts query's recordset whose SoldPrice value is greater than $200, enter the following:

Public Sub UpdateFirstSoldPrice10Percent()
Dim cnThisConnect As ADODB.Connection
Dim rcdContactProducts As New ADODB.RecordSet
Set cnThisConnect = CurrentProject.Connection
rcdContactProducts.Open "qryContactProducts", _
  cnThisConnect, adOpenKeyset, adLockOptimistic, _
  adCmdTable
rcdContactProducts.Filter = "SoldPrice > 200"
' Test the recordset EOF property for "not found"
If Not rcdContactProducts.EOF Then
  rcdContactProducts![SoldPrice] = _
   rcdContactProducts![SoldPrice] * 1.1
  rcdContactProducts.Update
  rcdContactProducts.MoveNext
End If

To insert a new row in a recordset, use the AddNew method to start a new row. Set the values of all required fields in the row and then use the Update method to save the new row. For example, to insert a new company in the Conrad Systems Contacts tblCompanies table, enter the following:

Dim cnThisConnect As ADODB.Connection
Dim rcdCompanies As New ADODB.RecordSet
Set cnThisConnect = CurrentProject.Connection
rcdCompanies.Open "tblCompanies", cnThisConnect, _
  adOpenKeyset, adLockOptimistic, adCmdTable
rcdCompanies.AddNew
rcdCompanies![CompanyName] = "Winthrop Brewing Co."
rcdCompanies![Address] = "155 Riverside Ave."
rcdCompanies![City] = "Winthrop"
rcdCompanies![StateOrProvince] = "WA"
rcdCompanies![PostalCode] = "98862"
rcdCompanies![PhoneNumber] = "(509) 555-8100"
rcdCompanies.Update

Other Uses for Object Methods

As you'll learn later in this tutorial in more detail, you must use a method of the DoCmd object to execute the equivalent of most macro actions within Visual Basic. You must use the RunCommand method of either the Application or DoCmd object to execute commands you can find on any of the Access menus.

You can also define a public function or subroutine (see the next section) within the module associated with a Form or Report object and execute that procedure as a method of the form or report. If your public procedure is a function, you must assign the result of the execution of the method to a variable of the appropriate type. If the public procedure is a subroutine, you can execute the form or report object method as a Visual Basic statement. For more information about object methods, find the topic about the object of interest in Help, and then click the Methods hyperlink.