| ADO also uses different sorts of recordsets, but they're defined differently than DAO recordset types. The two principal ways to specify an ADO recordset's properties are by setting them directly or as part of the recordset's Open method. For example Set rsProcs = New ADODB.Recordset With rsProcs .Source = "Procedures" .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & SourceFile .CursorType = adOpenDynamic .LockType = adLockOptimistic .CursorLocation = adUserServer End With
Using the recordset's Open method, you could use this statement: rsProcs.Open rsProcs.Open "Procedures", strConnectToShortStay, _ adOpenStatic, adLockOptimistic, adCmdTable
This example passes recordset parameters to the recordset's Open method; in order, its source, a string defining the connection, the cursor type, the lock type, and the cursor location. (See the following sections for discussions of the latter three parameters.) With an ADO recordset, you don't need to worry as you do with DAO about the recordset's source. You can use a table, a query, a SQL statement, and other sources without having to make sure that you've chosen the right type of recordset. The connection string is a parameter we've met before in ADO structures. It typically specifies a provider such as Microsoft.Jet.OLEDB.4.0 for an Access Jet database or SQLOLEDB.1 for SQL Server. It also provides information about the path to and name of a database, or the name of a server and a catalog for SQL Server. Other parameters are introduced in the next three sections. Using the CursorType Property The CursorType property is in one way similar to a DAO recordset's Type property: It determines whether changes that other users might make to the source's records show up in your recordset, and whether all the fields and records are brought into the recordset when it's opened. The values that the CursorType property can assume are as follows: adOpenDynamic This cursor type initially retrieves key values only. When you edit a record or otherwise access its fields, the remaining data for that record is retrieved. Changes made by other users to the source are reflected in the recordset, including adding and deleting records. This cursor type makes a greater demand on resources than do other types. adOpenForwardOnly This is the default setting. You don't have access to changes that other users might make after opening the recordset. You should use it only to move forward in the recordset; depending on conditions, you might encounter errors if you try to move, for example, from the final record to the first. It makes more efficient use of system resources than other cursor types. adOpenKeyset Like adOpenDynamic, the keyset type initially retrieves keys only. If other users edit field values in records, those modifications show up when you retrieve a full record. Records that are newly added to the source by other users are not added to the recordset. adOpenStatic Changes made by other users do not show up in the recordset. Access terms this cursor type a snapshot, but it's not the same as a DAO snapshot because you can make it updatable by setting its lock type (see the next section).
Using the LockType Property The LockType property enables you to control whether and how it's possible for your code to change the data in the recordset's source. There are four values: adLockBatchOptimistic All edits your code might make to the recordset are saved until an UpdateBatch method is encountered. At that point, the edits are saved to the source of the recordset. This isn't necessarily on a record-by-record basis: You could, for example, edit each record in the recordset before an update caused the changes to be saved. adLockOptimistic This is similar to setting an optimistic lock on a DAO recordset. During the editing process, the record isn't locked and can be edited by other users. It's locked during the recordset update and then released again. adLockPessimistic This is similar to a DAO pessimistic lock. Another user cannot edit a record that you've already begun to edit until after you've updated the recordset. adLockReadOnly This is the default setting. You cannot update the recordset. This setting does not, however, prevent another user from editing the source's records while you have it open. If you're doing nothing but viewing data or copying it to a target such as a worksheet, this is the most efficient choice.
Using the CursorLocation Property You might expect a cursor location to refer to the current record, but it actually refers either to the server or the client. If the database engine, such as Jet or SQL Server, manages the cursor, and thus manages the recordset, this property has been set to adUseServer. That's the default setting, and it means that the same engine that is managing all the other users is taking care of you. When there are many users, that can slow things down. You can take it easy on the database engine and let ADO manage the cursor by setting the CursorLocation property to adUseClient. You cannot set this property by way of the recordset's Open method. Instead, use a statement of the following type before invoking Open: rsProcs.CursorLocation = adUseClient
|
