The Recordset Object In ActiveX Data Objects

This Lesson explains the Recordset object (familiar to users of DAO), which is used to access and manipulate data on the record level. As you learned earlier, all data can be broken down int logical rows, each containing one or more fields. Each field, in turn, describes one specific piece of data that falls into a specific category that is common throughout all the other rows in that rowset. For instance, the Authors table of the Biblio.mdb Access database supplied with Visual Basic contains one row per Author in the table. It also has a field called Name, which is an attribute of every Author.

With the Recordset object, you can navigate through the multiple rows that make up a rowset. You can search for particular rows, move to a row that you previously marked, or move to the beginning or the end of your logical rowset, all with the Recordset object.

The Recordset object is also used to add, edit, or delete records. We can specify how the data provider executes batch commands, and we can also run queries on our data source to provide a customized, filtered view of records.

ActiveX Data Objects allows us to view our records, selected from our data source, in a number of different ways. The way in which our data is present ed to us is described by a cursor.

Cursors: Viewing a Recordset:-

Cursors present a logical view of a particular recordset. Once the records have been selected for us from our data source by opening our Recordset object, we must decide on how we would like them presented. This is done through a cursor. In ADO, there are four types of cursors:

• Dynamic cursor
• Keyset cursor
• Static cursor
• Forward-only cursor

In the following sections, the different cursor types are explained and an example at the end displays the various functions of each. To prepare your Recordset object to open with a particular cursor, use the CursorType property. The CursorType property is both readand write-enabled while the Recordset object is closed, but once it is opened, the CursorType property becomes read-only.

Not all providers support all cursor types. When the data provider cannot provide you with the cursor that you have specified because prerequisites could not be met, the data provider may automatically assign a different cursor type. When this occurs, you can read the CursorType property to see the kind of cursor that was actually used when opening the recordset.

Dynamic Cursor:-

The first type of cursor is a dynamic cursor. A dynamic cursor allows you to move freely throughout your recordset, without restrictions, except when your provider does not support bookmarks -- in this case, you cannot use bookmarks with the Dynamic cursor (see Section 5.3.5 later in this chapter for more information).

"Moving freely through the recordset" means that you may move the record pointer forward or backwards and to the beginning and end of the recordset. This may seem like an obvious functionality, but as you will soon see, not all cursors allow you to move the record pointer this freely.

One of the biggest benefits of using the dynamic cursor is that it allows you to see, in real time, all additions, changes, and deletions made by other users in your recordset. For instance, if another user adds a row to a data source, and that row would have been part of your recordset if you recreated it, that row immediately becomes part of your recordset.

The dynamic cursor is by far the most versatile cursor available to the ADO developer. The major drawback of the dynamic cursor is that it is extremely resource- and timeintensive because of its ability to show, in real time, changes made by other users. I strongly recommend that you use the dynamic cursor only on the rare occasions when speed is not a concern and you need the added functionality that this cursor alone offers.

To prepare your Recordset object to open a dynamic cursor, set the CursorType property to adOpenDynamic:

rst.CursorType = adOpenDynamic

Keyset Cursor:-

The keyset cursor is very similar to the dynamic cursor in functionality, with the exception of the real-time viewing of new records created, deleted, and modified by other users. This cursor is the most resource-intensive cursor available in ADO.

The keyset cursor thus allows changes made by other users to be visible to your currently opened recordset and blocks access to records that have been deleted by other users, just as with a dynamic cursor. However, when a record is added by another user to your data source, when you have an open keyset cursor, you will not see the new record until you recreate the recordset.

Unlike with the dynamic cursor, the ability of the data provider to offer the use of bookmarks is

mandatory. This is so that the keyset cursor can offer something called batch updates, which will

be covered in more detail later in this Lesons.

Use the keyset cursor instead of the dynamic cursor whenever possible to conserve resources.

To prepare your Recordset object to open a keyset cursor, set the CursorType property to adOpenKeyset:

rst.CursorType = adOpenKeyset

Figure 5-1 shows us the functionality supported by the keyset cursor. (The code for creating this informational dialog box is in the section "Cursor Type Example.")

Figure 5-1. Keyset cursor functionality

Static Cursor:-

The static cursor is vastly different than the previous two. It still allows your application to move freely through the recordset. The difference is that you are unable to dynamically view changes made to the records by other users within your static recordset.

The static cursor is like a snapshot of the data within your data source at a specific point in time. You could open a static recordset on your data source and immediately afterward have the entire data source deleted, but your static cursor would never see the changes. To prepare your Recordset object to open a static cursor, set the CursorType property to adOpenStatic:

rst.CursorType = adOpenStatic

As with keyset cursors, Static cursors require that bookmarks are supported by the data provider. Figure 5-2 shows the functionality supported by the static cursor.

Figure 5-2. Static cursor functionality

The static cursor is excellent when you need to generate reports on data, which cannot dynamically change. It is also very good when you are using a data source that you know for sure no other users will access while you are.

The static cursor is significantly faster than both the dynamic and the keyset cursors because it does not constantly check the data source to ensure that changes have not been made.

When you are using a client-side Recordset object with Remote Data Service ( RDS), your only choice of cursor type is the static cursor.

Forward-Only Cursor:-

The forward-only cursor is the fastest available. It is similar to the static cursor in that it presents a snapshot of the data at a particular point in time, but it lacks the ability to move the record pointer backwards. The forward-only cursor is the only type of cursor that does not allow the record pointer to be moved in this way.

Because the forward-only cursor is so limited in its abilities, there are no prerequisites to opening one. To prepare your Recordset object to open a forward-only cursor, set the CursorType property to adOpenForwardOnly:

rst.CursorType = adOpenForwardOnly

Figure 5-3 indicates the functionality supported by the forward-only cursor.

Figure 5-3. Forward-only cursor functionality

The forward-only cursor is very useful when you need to make only a single pass through your recordset, such as in some reporting scenarios. I strongly recommend using the forward-only cursor whenever your application can deal with its lack of abilities, because it is so fast.

CursorType Example:-

The example in this section will demonstrate the different available functions for each of the four cursor types.

First, create a new project with one form. Add the controls listed in Table 5-1, setting their values as specified in the second and third columns. Figure 5-4 shows the result.

Figure 5-4. The CursorType example in design mode

Table 5-1. The CursorType Example Control Settings

Control	Property	Value
Command Button	Name	cmdCursorType
	Index	0
	Caption	ForwardOnly
Command Button	Name	cmdCursorType
	Index	1
	Caption	Keyset
Command Button	Name	cmdCursorType
	Index	2
	Caption	Dynamic
Command Button	Name	cmdCursorType
	Index	2
	Caption	Static
Command Button	Name	cmdClose
	Caption	&Close
Label	Caption	Cursor Opened:
Label	Name	lblCursorType
Label	Caption	Add New:
Label	Caption	Approx. Position:
Label	Caption	Bookmark:
Label	Caption	Delete:
Label	Caption	Find:
Label	Caption	Hold Records:
Label	Caption	Move Previous:
Label	Caption	Notify:
Label	Caption	Resync:
Label	Caption	Update:
Label	Caption	Update Batch:
Label	Name	lblBoolean
	Index	0
	Tag	16778240
Label	Name	lblBoolean
	Index	1
	Tag	16384
Label	Name	lblBoolean
	Index	2
	Tag	8192
Label	Name	lblBoolean
	Index	3
	Tag	16779264
Label	Name	lblBoolean
	Index	4
	Tag	524288
Label	Name	lblBoolean
	Index	5
	Tag	256
Label	Name	lblBoolean
	Index	6
	Tag	512
Label	Name	lblBoolean
	Index	7
	Tag	262144
Label	Name	lblBoolean
	Index	8
	Tag	131072
Label	Name	lblBoolean
	Index	9
	Tag	16809984
Label	Name	lblBoolean
	Index	10
	Tag	65536

Once you have all of the controls in place and you have set the necessary properties, you can begin to enter the code. Begin by entering the cmdClose_Click event to end the application:

Private Sub cmdClose_Click( )

Unload Me

End Sub

Next, enter the bulk of the code in the cmdCursorType_Click event:

Private Sub cmdCursorType_Click(Index As Integer)

Dim rst As ADODB.Recordset

Dim nCount As Integer

Const FORWARD_ONLY = 0

Const KEYSET = 1

Const DYNAMIC = 2

Const STATIC = 3

Set rst = New ADODB.Recordset

Select Case (Index)

Case FORWARD_ONLY:

rst.CursorType = adOpenForwardOnly

lblCursorType.Caption = "Forward Only"

Case KEYSET:

rst.CursorType = adOpenKeyset

lblCursorType.Caption = "Keyset"

Case DYNAMIC:

rst.CursorType = adOpenDynamic

lblCursorType.Caption = "Dynamic"

Case STATIC:

rst.CursorType = adOpenStatic

lblCursorType.Caption = "Static"

End Select

If (rst.State & adStateOpen) Then rst.Close

rst.Open "Authors", _

"DSN=BiblioDSN", _

, _

adLockPessimistic

For nCount = 0 To 10

If (rst.Supports(lblBoolean(nCount).Tag)) Then

lblBoolean(nCount).Caption = "Supported"

Else

lblBoolean(nCount).Caption = "---"

End If

Next nCount

rst.Close

Set rst = Nothing

End Sub

This code determines which button has been pressed by the Index variable passed to the function. With this information, the application opened the Authors table of the DSN with the correct cursor type. Once the recordset is opened, the application loops through all of the lblBoolean labels on the form. Each label represents a function that the current cursor may or may not support. By the value stored in that label's Tag property, the Recordset can determine whether the functionality is supported. Each of the values in the Tag properties directly corresponds to the functions enumeration value from the CursorOptionEnum enumeration shown in Table 5-2.

Table 5-2. The CursorOptionEnum Enumeration

Value	Description
adAddNew	Indicates that the recordset can use the AddNew method to add new records.
adApproxPosition	Indicates that the recordset supports the AbsolutePosition and AbsolutePage properties.
adBookmark	Indicates that the recordset supports the Bookmark property.
adDelete	Indicates that the recordset can use the Delete method to delete records.
adHoldRecords	Indicates that the recordset allows more records to be modified before committing current changes.
adMovePrevious	Indicates that the recordset can use the MovePrevious method to step backwards in a recordset.
adResync	Indicates that the recordset can use the Resync method to update the records in the recordset from the underlying data source.
adUpdate	Indicates that the recordset can use the Update method to update information that has been modified within the recordset.
adUpdateBatch	Indicates that the recordset can use the UpdateBatch and CancelBatch methods to support batch processing of edits.

Finally, enter the code for the Form_Load event, which kicks off the application by invoking the Forward Only command button:

Private Sub Form_Load( )

cmdCursorType_Click (0)

End Sub

Once this project is built, press the Dynamic button to see the application illustrated in Figure 5-5.

Figure 5-5. Dynamic cursor functionality

If you are having trouble starting the application, you may need to set up a System DSN (Data Source Name) -- see "Installing the DSNs" in This Lesson.

The examples in this chapter use the Biblio.mdb Access database extensively. In order for these examples to work correctly (and to maintain their simplicity), it is necessary to remove all relationships from within the tables of the Biblio.mdb database. It is suggested that you make a copy of the Biblio.mdb database (in a working directory), open it up with Access, and remove all of the relationships. Once this is done, you can use the ODBC Data Source Administrator to set the System DSN, BiblioDSN, to your copy of the Biblio.mdb database as described in This Lesson.