Introduction to ADO Recordsets

The Recordset object represents a set of records in a table, or a set of records returned by executing a stored query or an SQL statement. The Recordset object is one of the three most-used ADO objects (the other two are Connection and Command). What you can do with a recordset depends entirely on the built-in capabilities of its OLE DB provider.

You can open a recordset by using the recordset's Open method. The information needed to open a recordset can be provided by first setting properties, then calling the Open method, or by using the Open method's parameters like this:

rst.Open [Source], [ActiveConnection], [CursorType], [LockType], [CursorLocation], [Options]

Notice that all the parameters are optional (they appear in square brackets). If you decide that you don't want to pass parameters, then you will need to use a different syntax to open a recordset. For example, examine the following code block:

With rst

.Source = strSQL .ActiveConnection = strConnect .CursorType = adOpenStatic .LockType = adLockOptimistic .CursorLocation = adUseClient .Open Options := adCmdText End with

The code segment above opens a recordset by first setting properties of the Recordset object, then calling its Open method. Notice that the names of the required recordset properties are equivalent to the parameter names listed earlier. The values assigned to each property are discussed later. You will become

Part II

familiar with both methods of opening a recordset as you work with this chapter's example procedures.

Let's return to the syntax of the recordset's Open method, which specifies the parameters. Needless to say, you need to know what each parameter is and how it is used.

The Source parameter determines where you want your records to come from. The data source can be an SQL string, a table, a query, a stored procedure or view, a saved file, or a reference to a Command object. Later in this chapter you will learn how to open a recordset based on a table, a query, and an SQL statement.

The ActiveConnection parameter can be an SQL string that specifies the connection string or a reference to a Connection object. This parameter tells where to find the database as well as what security credentials to use.

Before we discuss the next three parameters, you need to know that the ADO recordsets are controlled by a cursor. The cursor determines whether you are permitted to scroll through the recordset backward and forward or forward only, or whether you can use it read-only or you can update the data, and whether changes made to the data are visible to other users.

The ADO cursors have three functions specified by the following parameters:

■ CursorType

■ CursorLocation

Before you choose the cursor, you need to think of how your application will use the data. Some cursors yield better performance than others. It's important to determine where the cursor will reside and whether changes made while the cursor is open need to be visible immediately. The following subsection should help you in choosing the correct cursor.

Cursor Types

The CursorType parameter specifies how the recordset interacts with the data source and what is allowed or not allowed when it comes to data changes or movement within the recordset. This parameter can take one of four constants: adOpenForwardOnly (0), adOpenKeyset (1), adOpenDynamic (2), and adOpenStatic (3).

To quickly find out what types of cursors are available, switch to the Visual Basic window and open the Object Browser. Select ADODB from the Project/ Library drop-down list box and type CursorType in the Search Text box, as shown in Figure 13-1.

Creating and Manipulating Databases with ADO

" Object Browser

Creating and Manipulating Databases with ADO

" Object Browser

|fl[lOUB

< jm

j jj

| C<N50iTya>e

_M al*

searcn Kesuus

Library

1 Class

I Member

■F-- ADODB

CursorTypeEnum

if- ADODB

«51 Recordset

uS* CursorType

Classes a3 ConnectOptionEnum # ConneetPrornptEnum zP CopyffecordOptionsEnum

CursorLocationEnum & CursorOjatonEnum

M e rn b e rs of CurscrTypeDiurn j-1

I) adOpenForwardOnly 3 adOpenKeryset <3 adOpenStatic

Classes a3 ConnectOptionEnum # ConneetPrornptEnum zP CopyffecordOptionsEnum

CursorLocationEnum & CursorOjatonEnum

¿P LCursorTypeEnum iP DataTypeErrum ¿P EditModeEnum ErrorValueEnum

M e rn b e rs of CurscrTypeDiurn j-1

I) adOpenForwardOnly 3 adOpenKeryset <3 adOpenStatic

Const adOpen Dynamic = 2

Figure 13-1: The Object Browser lists four predefined constants you can use to specify the cursor type to be retrieved.

When the cursor type is dynamic (adOpenDynamic = 2), users are allowed to view changes other users made to the database. The dynamic cursor is not supported by the Jet 4.0 engine in Microsoft Office Access. To use this cursor, you must use other OLE DB providers, such as MSDASQL or SQLOLEDB. Using the dynamic cursor you can move back and forth in the recordset.

When the cursor type is forward-only (adOpenForwardOnly = 0), additions, changes, and/or deletions made by other users are not visible. This is the default and fastest cursor because it only allows you to scroll forward in the recordset.

When the cursor type is keyset driven (adOpenKeyset =1), you can scroll back and forth in the recordset; however, you cannot view records added or deleted by another user. Use the recordset's Requery method to overcome this limitation.

When the cursor type is static (adOpenStatic = 3), all the data is retrieved as it was at a point in time. This cursor is desirable when you need to find data or generate a report. You can scroll back and forth within a recordset, but additions, changes, or deletions by other users are not visible. Use this cursor to retrieve an accurate record count.

You must set the CursorType property before opening the recordset with the Open method. Otherwise Access will create a forward-only recordset.

Part II

Lock Types

After you choose a cursor type, you need to specify how the ADO should lock the row when you make a change. The LockType parameter specifies whether the recordset is updatable. The default setting for LockType is read-only. The LockType predefined constants are listed in the Object Browser, as shown in Figure 13-2.

Object Browser

|ACOUB

z] «

1 LochTyiie

_ilikl*

searcn Kesuus

IJIiirarv

1 Class

1 Member

■fi ADO DE:

dp LockTypeEnum

IT"- ADODB

«51 Recordset

ES" LockType

Classes s3 FilterGroupEnum ¿P GetRowsOptionEnum ^ IsolationLevelEnum •s3 LineSeparatorEnum ¿P Cqc kTypeEnu m _

Members of 'LockTypeEnum'

¿J adLockOpiimistic 3 adLockPessimistic <3 adLcckReadOnty

Classes s3 FilterGroupEnum ¿P GetRowsOptionEnum ^ IsolationLevelEnum •s3 LineSeparatorEnum ¿P Cqc kTypeEnu m _

& MarshaiOptionsEnum jP MoveRecordOptionsEnum sP QbjectStateEnum & ParameterAttributesEnurn j^J

Members of 'LockTypeEnum'

¿J adLockOpiimistic 3 adLockPessimistic <3 adLcckReadOnty

Const adLock6atchOptimistic " 4

Figure 13-2: The Object Browser lists four predefined constants that you can use to specify what type of locking ADO should use when you make a change to the data.

When the LockType property is batch optimistic (adLockBatchOptimistic = 4), batch updates made to the data are stored locally until the UpdateBatch method is called, during which all pending updates are committed all at once. Until the UpdateBatch method is called, no locks are placed on edited data. Batch optimistic locking eliminates network roundtrips that normally occur with optimistic locking (adLockOptimistic) when users make changes to one record and move to another. With batch optimistic locking a user can make all the changes to all the records, then submit them as a single operation.

When the LockType property is optimistic (adLockOptimistic = 3), no locks are placed on the data until you attempt to save a row. Records are locked only when you call the Update method, and the lock is released as soon as the Save operation is completed. Two users are allowed to update a record at the same time. Optimistic locking allows you to work with one row at a time. If you need to make multiple updates, it's better to save them all at once by using batch optimistic locking.

Creating and Manipulating Databases with ADO

When the LockType property is pessimistic (adLockPessimistic = 2), all the records are locked as soon as you begin editing a record. The record remains locked until the edit is committed or cancelled. This type of lock guarantees that two users will not make changes to the same record. If you use pessimistic locking, ensure that your code does not require any input from the users. You certainly don't want a scenario where a user opens a record and makes a change, then leaves for lunch without saving the record. In that case, the record is locked until the user comes back and saves or discards the edit. In this situation, it is better to use optimistic locking.

When the LockType property is read-only (adLockReadOnly =1), you will not be able to alter any data. This is the default setting.

Cursor Location

The CursorLocation parameter determines whether ADO or the SQL server database engine manages the cursor. Cursors use temporary resources to hold the data. These resources can be memory, a disk paging file, temporary disk files, or even temporary storage in the database.

When a cursor is created and managed by ADO, the recordset is said to be using a client-side cursor (adUseClient). With the client-side cursor, all the data is retrieved from the server in one operation and is placed on the client computer. Because all the requested data is available locally, the connection to the database can be closed and reopened only when another set of data is needed. Since the entire result set has been downloaded to the client computer, browsing through the rows is very fast.

When a cursor is managed by a database engine, the recordset is said to be using a server-side cursor (adUseServer). With the server-side cursor, all the data is stored on the server and only the requested data is sent over the network to the user's computer. This type of cursor can provide better performance than the client-side cursor when excessive network traffic is an issue. However, it's important to point out that a server-side cursor consumes server resources for every active client and, because it provides only single-row access to the data, it can be quite slow.

It is recommended that you use the server-side cursor for working with local Access databases, and the client-server cursor when working with remote Access databases or SQL Server databases.

The CursorLocation predefined constants are listed in the Object Browser, as shown in Figure 13-3.

Part II

Figure 13-3: The Cursor-Location parameter of the recordset's Open method can be set by using the adUseClient or adUseServer constant.

The Options Parameter

The Options parameter specifies the data source type being used. Similar to the parameters related to cursors, the Options parameter can take one of many values, as outlined in Figure 13-4.

Figure 13-4: The Options parameter of the recordset's Open method is supplied by the constant values listed under the CommandText property of the Command object.

When the Options parameter is set to adCmdFile (256), it tells the ADO that the source of the recordset is a path or filename. ADO can open recordsets based on files in different formats.

When the Options parameter is set to adCmdStoredProc (4), it tells the ADO that the source of the recordset is a stored procedure or parameterized query.

Creating and Manipulating Databases with ADO

When the Options parameter is set to adCmdTable (2), it tells the ADO that the source of the recordset is a table or view. The adCmdTable constant will cause the provider to generate an SQL query to return all rows from a table or view by prepending SELECT * FROM in front of the specified table or view name.

When the Options parameter is set to adCmdTableDirect (512), it tells the ADO that the Source argument should be evaluated as a table name. How does this constant differ from adCmdTable? The addCmdTableDirect constant is used by OLE DB providers that support opening tables directly by name, using an interface called IOpenRowset, instead of an ADO Command object. Since the IOpenRowset method does not need to build and execute a Command object, it results in increased performance and functionality.

When the Options parameter is set to adCmdText (1), it tells the ADO that you are using an SQL statement to open the recordset. When the Options parameter is set to adCmdUnknown (8), it tells the ADO that the command type in the Source argument is unknown. This is the default, which is used if you don't specify any other option. By using the adCmdUnknown constant, or not specifying any constant at all for the Options parameter, you force ADO to make an extra roundtrip to the server to determine the source type. As you would expect, this will decrease your VBA procedure's performance; therefore you should use adCmdUknown only if you don't know what type of information the Source parameter will contain.

^^ Note: Not all options are supported by all data providers. For example, the Microsoft Jet OLE DB Provider does not support the adCmdTableDirect cursors.

In addition to specifying the type of CommandText in the Options parameter (see Figure 13-4), you can pass additional information in the Options parameter. For example, telling ADO how to execute the CommandText property by specifying whether ADO should wait while all the records are being retrieved or should continue asynchronously.

Asynchronous Record Fetching

Asynchronous fetching is an ADO feature that allows some records to be downloaded to the client while the remaining records are still being fetched from the database. As soon as the user sees some records, he or she can begin paging through them. The user does not know that only a few records have been returned. As he or she pages through the rows backward and forward, a new connection is made to the server and more records are fetched and passed to the client. Once all records have been returned, paging is very quick because all records are on the client. Asynchronous fetching makes it seem to the user that the data retrieval is pretty fast. The downside is that records cannot be sorted until they have all been downloaded.

Part II

Additional Options parameters are listed and described below. Note that only the first three constants (adAsyncExecute, adAsyncFetch, and adAsyncFetchNonBlocking) can be used with the recordset's Open method.

Other constants are used with the Command or Connection Execute method. adAsyncExecute (16) — This tells ADO to execute the command asynchronously, meaning that all requested rows are retrieved as soon as they are available. Using adAsyncExecute enables the application to perform other tasks while waiting for the cursor to populate. Note that the adAsyncExecute constant cannot be used with adCmdTableDirect.

adAsyncFetch (32) — Using this constant requires that you specify a value greater than 1 for the recordset's CacheSize property. The CacheSize property is used to determine the number of records ADO will hold in local memory. For example, if the cache size is 100, the provider will retrieve the first 100 records after first opening the Recordset object. The adAsyncFetch constant tells ADO that the rows remaining after the initial quantity specified in the CacheSize property should be retrieved asynchronously.

adAsyncFetchNonBlocking (64) — This option tells ADO that it should never wait for a row to be fetched. The application will continue execution while records are being continuously extracted from a very large data file. If the requested recordset row has not been retrieved yet, the current row automatically moves to the end of the file (causing the recordset's EOF property to become True). In other words, the data retrieval process will not block other processes.

Note that adAsynchFetchNonBlocking has no effect when the adCmd-TableDirect option is used to open the recordset. Also, adAsyncFetchNon-Blocking is not supported with a Server cursor (adUseServer) when you use the ODBC provider (MSDASQL).

adExecuteNoRecords (128) — This option tells ADO not to expect any records when the command is executed. Use this option for commands that do not return records, such as INSERT, UPDATE, or DELETE. Use the adExecuteNoRecords constant with adCmdText to improve the performance of your application. When this option is specified, ADO does not create a Recordset object and does not set any cursor properties. Note that adExecuteNoRecords can only be passed as an optional parameter to the Command or Connection Execute method and cannot be used when opening a recordset.

adExecuteStream (256) — Indicates that the results of a Command execution should be returned as a stream. The adExecuteStream constant can only be passed as an optional parameter to the Command or Execute method and it cannot be used when opening a recordset. adExecuteRecord (512) — Indicates that the value of the CommandText property is a command or stored procedure that returns a single row as a Record object (a Record object represents one row of data).

Creating and Manipulating Databases with ADO

adOptionUnspecified (-1) — Indicates that the command is unspecified. This is the default option.

Note that similar to adExecuteNoRecords, adExecuteStream, and adExecuteRecord, this constant can only be passed as an optional parameter to the Command or Connection Execute method and cannot be used when opening a recordset.

0 0

Post a comment