Share this Page URL

Chapter 17. Using Visual Basic to Manage... > Working with Recordsets in ADO - Pg. 609

Using Visual Basic to Manage Data Cursor Type and Constant Static adOpenStatic Description 609 A static cursor provides a snapshot of a recordset at a particular point. It allows backward and forward scrolling through the records, but any changes to the data made by other users are not visible. A static cursor is appropriate when you're opening a recordset to use with a report. A forward-only cursor is the default ADO cursor type. This type of cursor offers rapid access to the records in a recordset but provides only forward scrolling of the records. Specify a different type of cursor before opening the recordset if you don't want ADO to use adOpenForwardOnly. Forward-Only adOpenForwardOnly Using the LockType Property The second constant we've used, adLockOptimistic, sets the Recordset object's LockType property and manages whether and how a recordset can be updated after you open it. When you create and open a recordset using ADO, the recordset is opened as read-only ( adLockReadOnly ) by default. The examples shown earlier in this section would not perform as expected if we did not change the Recordset object's LockType property to adLockOptimistic. When you use adLockOptimistic, a re- cordset can be updated after it is opened. Records are locked only when the Recordset object's Update method is called, at which point changes are committed to the database. This setting could lead to conflicts because more than one user could be making changes to the same record at the same time. In a situation in which more than one user is likely to be working with the same data, you can use adLockPessimistic. With this setting, a record is locked in the data source when changes to the record are entered--in other words, as soon as a user starts making changes to the record. Pes- simistic locking helps avoid conflicts in updating data, but this setting also prevents users from getting to data while a record is locked. With the setting adLockBatchOptimistic, records can be edited until a batch of records are updated. Setting the CursorType and LockType Properties Directly Instead of specifying the cursor type and lock type as arguments to the Recordset object's Open method, you can also set the properties directly by using code such as the following: Set adoRst = New ADODB.Recordset adoRst.CursorType = adOpenKeyset adoRst.LockType = adLockOptimistic adoRst.Open ("MarketingCampaigns") Moving from Record to Record in a Recordset The Recordset object includes several methods that let you move between records in a recordset. The MoveNext method, which moves one record forward in the recordset, is often used in Do ... While loops--for example, when you use a loop to perform a common operation on each record in a recordset, such as increasing a field's value in each record by a set percentage. Other methods you can use to navigate through a recordset include MoveFirst, MoveLast, and MovePrevious. The Recordset object also has a method simply named Move. When calling the Move method, you provide a number that represents the number of records you want to move from the current position. (A positive number moves forward in the recordset. If the recordset can be scrolled backward, you can provide a negative number to move in that direction.) When you move between records, you need to keep track of whether the position is at the first record or the last record in the recordset. You can use the Recordset object's EOF and BOF properties (which we introduced in Chapter 5) to keep track of whether the position is at the end or the beginning of a recordset. If you attempt to move forward in a recordset when the EOF property is True, for example, ADO generates an error.