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.
