Recordset

ADO recordsets represent sets of records in a database, much like DAO recordsets, though their attributes are more generic. An ADO recordset can be based on a table, query, SQL statement, or Command object. The TestForwardReadOnly procedure listed here uses a saved select query as the recordset source:

Private Sub TestForwardReadOnly()

On Error GoTo ErrorHandler

Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset

Create a connection to the current database.

Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset

Create a recordset based on a select query.

rst.Open Source:="qryCompanyAddresses", _ ActiveConnection:=cnn.ConnectionString, _ CursorType:=adOpenForwardOnly, _ LockType:=adLockReadOnly

Iterate through the query, and print values from fields to the Immediate window.

Do While Not rst.EOF

Debug.Print "Company ID: " & rst![CompanyID] _ & vbCrLf & vbTab & "Category: " _ & rst![Category] _

& vbCrLf & vbTab & "Company Name: " _ & rst![Company] & vbCrLf rst.MoveNext

Loop

ErrorHandlerExit:

Close the Recordset and Connection objects.

If Not rst Is Nothing Then

If rst.State = adStateOpen Then rst.Close Set rst = Nothing End If End If

If Not cnn Is Nothing Then

If cnn.State = adStateOpen Then cnn.Close Set cnn = Nothing End If End If

Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

ADO recordsets have four types of cursors (cursors in ADO are roughly equivalent to the DAO recordset types), as described in the following two tables. Each cursor type supports different methods depending on the setting of the LockType argument. The two most commonly used lock types are the read-only (named constant: adLockReadOnly) and optimistic (named constant: adLockOptimistic) type. Table 5.4 lists the ADO cursor types, with their numeric equivalents, and Table 5.5 lists the most commonly used cursor and lock type combinations for working with Access data.

TABLE 5.4

ADO Recordset Cursor Type Named Arguments

Cursor Type

Named Constant

Numeric Value

Dynamic

adOpenDynamic

2

Keyset

adOpenKeyset

1

Static

adOpenStatic

3

Forward-only

adOpenForwardOnly

0

TABLE 5.5

ADO Recordset Lock Type Named Arguments

Lock Type

Named Constant

Numeric Value

Read-only

adLockReadOnly

1

Optimistic

adLockOptimistic

3

Batch Optimistic

adLockBatchOptimistic

4

Pessimistic adLockPessimistic 2

Pessimistic adLockPessimistic 2

Sometimes the ADO recordset that is created is not the type you specify in the CursorType argument, depending on the lock type. In particular, if you specify the adLockOptimistic lock type for any cursor type, you will actually get a Keyset cursor. To determine the actual recordset type, use the following statement (see Table 5.4 to convert the numeric type to its matching named constant):

Debug.Print "Recordset cursor type: " _ & rst.CursorType

For a more advanced determination of the actual cursor type and lock type of a newly created recordset, run the following procedure, substituting the desired cursor and lock type in the rst.Open line:

Private Sub TestMethodsSupported()

On Error Resume Next

Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim strDBName As String

Dim strConnectString As String

Dim strSQL As String

Dim strCursorType As String

Dim strLockType As String

Dim strCurrentPath As String

Dim fso As New Scripting.FileSystemObject

Dim fil As Scripting.File

Dim strDBNameAndPath As String

Dim strPrompt As String

Create a connection to an external database.

strCurrentPath = Application.CurrentProject.Path & "\" strDBName = "Northwind.mdb"

strDBNameAndPath = strCurrentPath & strDBName

Attempt to find the database, and put up a message if it is not found.

Set fil = fso.GetFile(strDBNameAndPath) If fil Is Nothing Then strPrompt = "Can't find " & strDBName & " in " _

& strCurrentPath & "; please copy it from the " _ & "Office11\Samples subfolder under the main " _ & "Microsoft Office folder " _ & "of an earlier version of Office" MsgBox strPrompt, vbCritical + vbOKOnly GoTo ErrorHandlerExit End If

On Error GoTo ErrorHandler

Set cnn = New ADODB.Connection Set rst = New ADODB.Recordset

Need to specify the Jet 4.0 provider for connecting to Access databases.

With cnn

.Provider = "Microsoft.Jet.OLEDB.4.0" .Open strDBNameAndPath strConnectString = .ConnectionString End With

Use a SQL string to create a filtered recordset.

strSQL = "SELECT CompanyName, ContactName, City " _ & "FROM Suppliers " _ & "WHERE Country = 'Australia' " _

& "ORDER BY CompanyName;"

Modify the cursortype and locktype arguments as desired to test what type of recordset is created when the procedure is run.

rst.Open Source:=strSQL, _

ActiveConnection:=strConnectString, _ CursorType:=adOpenForwardOnly, _ LockType:=adLockOptimistic strCursorType = Switch(rst.CursorType = _ adOpenDynamic, _

"Dynamic (" & adOpenDynamic & ")", _ rst.CursorType = adOpenForwardOnly, _ "Forward-only (" _ & adOpenForwardOnly & ")", _

rst.CursorType = adOpenKeyset, "Keyset (" _ & adOpenKeyset & ")", _

rst.CursorType = adOpenStatic, "Static (" _ & adOpenStatic & ")")

strLockType = Switch(rst.LockType = _ adLockOptimistic, _

"Optimistic (" & adLockOptimistic & ")", _ rst.LockType = adLockReadOnly, "Read-only (" & adLockReadOnly & ")", _ rst.LockType = adLockBatchOptimistic, _ "BatchOptimistic (" _ & adLockBatchOptimistic & ")", _ rst.LockType = adLockPessimistic, _ "Pessimistic (" _ & adLockPessimistic & ")")

Debug.Print "Recordset cursor/lock type: " _

& strCursorType & ", " & strLockType & vbCrLf Debug.Print "AddNew supported? " _

& rst.Supports(adAddNew) Debug.Print "Delete supported? " _

& rst.Supports(adDelete) Debug.Print "Find supported? " _

& rst.Supports(adFind) Debug.Print "MovePrevious supported? " _

& rst.Supports(adMovePrevious) Debug.Print "Update supported? " _ & rst.Supports(adUpdate)

ErrorHandlerExit:

Close the Recordset and Connection objects.

If Not rst Is Nothing Then

If rst.State = adStateOpen Then rst.Close Set rst = Nothing End If

End If

If Not cnn Is Nothing Then

If cnn.State = adStateOpen Then cnn.Close Set cnn = Nothing End If

End If

Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit End Sub

Because other types of cursors are converted to keysets when you use optimistic locking for an ADO recordset, you may as well specify the keyset cursor when you create the recordset, because that is what you are going to get. See Table 5.6 for the details.

TABLE 5.6

ADO Recordset Cursor/Lock Type Combinations

Cursor Type Named Constant Lock Type Named Constant Available Methods adOpenDynamic (converts to adOpenKeyset)

AdLockOptimistic

AddNew

Delete

Find

MoveFirst

MovePrevious

MoveNext

MoveLast

Update adOpenDynamic (converts to adOpenStatic)

adLockReadOnly

Find

MoveFirst MovePrevious MoveNext MoveLast adOpenKeyset

AdLockOptimistic

AddNew

Delete

Find

MoveFirst

MovePrevious

MoveNext

MoveLast

Update adOpenKeyset

AdLockReadOnly

Find

MoveFirst MovePrevious MoveNext MoveLast continued

TABLE 5.6

(continued)

Cursor Type Named Constant

Lock Type Named Constant

Available Methods adOpenStatic

(converts to adOpenKeyset)

AdLockOptimistic

AddNew

Delete

Find

MoveFirst

MovePrevious

MoveNext

MoveLast

Update adOpenStatic

AdLockReadOnly

Find

MoveFirst MovePrevious MoveNext MoveLast adOpenForwardOnly (converts to adOpenKeyset)

AdLockOptimistic

AddNew

Delete

Find

MoveFirst

MovePrevious

MoveNext

MoveLast

Update adOpenForwardOnly

AdLockReadOnly

Find

MoveFirst MoveNext MoveLast

0 0

Post a comment