Cursor Type Named Constant Lock Type Named Constant Available Methods
adOpenDynamic AdLockOptimistic AddNew
(converts to adOpenKeyset) Delete
Find
(converts to adOpenStatic) MoveFirst
MovePrevious
TABLE 5.6 (continued)
Cursor Type Named Constant Lock Type Named Constant Available Methods
adOpenStatic AdLockOptimistic AddNew
(converts to adOpenKeyset) Delete
Find
(converts to adOpenKeyset) Delete
Find
A dynamic cursor (DAO equivalent: dbOpenDynaset) lets you view additions, changes, or deletions made by other users. All types of movement through the recordset are allowed.
Keyset
In a recordset with a keyset cursor (there is no equivalent DAO recordset type), you can add, change, and delete data in records, but you can’t see records that other users add or delete.
However, you can see changes made by other users. With an optimistic (adLockOptimistic) lock type, you can modify the data; if you don’t need to modify the data, use a read-only lock type (adLockReadOnly) for faster data access.
The following TestKeysetOptimisticprocedure adds a new record to the tlkpCategories table, and sets the value of a field from input provided by the user, after checking whether the cate-gory name provided by the user has already been used:
Private Sub TestKeysetOptimistic() On Error GoTo ErrorHandler
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strCategory As String Dim strPrompt As String Dim strTitle As String Dim strSearch As String Create a connection to the current database.
Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset Create a recordset based on a table.
rst.Open Source:=”tlkpCategories”, _
ActiveConnection:=cnn.ConnectionString, _ CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic CategoryName:
Add a new record, getting a field value from the user.
strPrompt = “Please enter new category name”
strTitle = “New category”
strCategory = Nz(InputBox(prompt:=strPrompt, _ Title:=strTitle))
If strCategory = “” Then GoTo ErrorHandlerExit Else
strSearch = “[Category] = “ & Chr$(39) _
& strCategory & Chr$(39)
Debug.Print “Search string: “; strSearch With rst
.MoveLast .MoveFirst
Debug.Print .RecordCount _
& “ records initially in recordset”
Check whether this category name has already been used — if the search fails, the cursor will be at the end of the recordset.
rst.Find strSearch If rst.EOF = False Then
strPrompt = Chr$(39) & strCategory _
& Chr$(39) & “ already used; “ _
& “please enter another category “ _
& “name”
strTitle = “Category used”
MsgBox prompt:=strPrompt, _
Buttons:=vbExclamation + vbOKOnly, _ Title:=strTitle
strPrompt = Chr$(39) & strCategory _
& Chr$(39) & “ added to table”
strTitle = “Category added”
MsgBox prompt:=strPrompt, _
Buttons:=vbInformation + vbOKOnly, _ Title:=strTitle
Debug.Print .RecordCount _
& “ records in recordset after adding”
End If End With End If
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:
MsgBox “Error No: “ & Err.Number _
& “; Description: “ & Err.Description Resume ErrorHandlerExit
End Sub
The code prints the search string (always useful for debugging) and the number of records in the recordset, before and after adding the new record, to the Immediate window:
Search string: [Category] = ‘Firmware’
29 records initially in recordset 30 records in recordset after adding
Static
The static cursor type (DAO equivalent: dbOpenSnapshot) provides a static copy of a set of records, for viewing or printing data. All types of movement through the recordset are allowed.
Additions, changes, or deletions made by other users are not shown. For fast access to data that you don’t need to modify, where you don’t need to view other users’ changes and you do need to be able to move both forward and backward in the recordset, use a static cursor and the adLockReadOnlylock type, as in the following TestStaticReadOnlyprocedure. If you do need to modify the data, but don’t need to see other users’ changes, use the adLockOptimistic lock type instead (the cursor type will change to keyset, as noted previously).
The TestStaticReadOnlyprocedure sets up a connection to the Northwind database, opens a filtered recordset based on a table in the database, and then iterates through the recordset, printing information from its fields to the Immediate window. Note that once an ADO recordset has been created, many of the same methods can be used to work with it as for a DAO database (BOF, EOF, Find*, Move*):
Private Sub TestStaticReadOnly() On Error Resume Next
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strDBName As String
Dim strDBNameAndPath As String Dim strConnectString As String Dim strSQL As String
Dim strCurrentPath As String
Dim fso As New Scripting.FileSystemObject Dim fil As Scripting.File
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;”
rst.Open Source:=strSQL, _
ActiveConnection:=strConnectString, _ CursorType:=adOpenStatic, _
LockType:=adLockReadOnly
Iterate through the recordset, and print values from fields to the Immediate window.
With rst .MoveLast .MoveFirst
Debug.Print .RecordCount _
& “ records in recordset” & vbCrLf Do While Not .EOF
Debug.Print “Australian Company name: “ _
& ![CompanyName] _
& vbCrLf & vbTab & “Contact name: “ _
& ![ContactName] _
& vbCrLf & vbTab & “City: “ & ![City] _
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:
MsgBox “Error No: “ & Err.Number _
& “; Description: “ & Err.Description Resume ErrorHandlerExit
End Sub
The following information is printed to the Immediate window:
2 records in recordset
Australian Company name: G’day, Mate Contact name: Wendy Mackenzie City: Sydney
Australian Company name: Pavlova, Ltd.
Contact name: Ian Devling City: Melbourne
Forward-only
The forward-only cursor (DAO equivalent: dbOpenForwardOnly) allows only forward move-ment through a recordset and doesn’t show additions, changes, or deletions made by other users.
It is the default cursor type. For the fastest access to data that you don’t need to modify, use a forward-only cursor and the adLockReadOnlylock type, as in the TestForwardReadOnly procedure that follows; if you do need to modify the data, use the adLockOptimisticlock type instead:
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 its 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:
MsgBox “Error No: “ & Err.Number _
& “; Description: “ & Err.Description Resume ErrorHandlerExit
End Sub
Data from each record is printed to the Immediate window; the last two records’ data is listed here:
Company ID: Yclept Yarbro Category: Books
Company Name: Yclept Yarbro Company ID: ZDExpos
Category: Computer Company Name: ZDExpos
Record
An ADO Record object represents a set of data, which may be from a recordset or a non-database source. When working with Access data, the Record object is a single row from a recordset, or a one-row recordset. There are many specialized uses of Record objects based on non-Access data (in particular, for working with hierarchical data and displaying it in TreeView controls), but when working with Access data in VBA code there is no reason to use the Record object, because you can reference fields as needed on the current record in a recordset without creating a Record object.
Stream
A Stream object represents a stream of data from a text file, XML document, or web page. Because this object doesn’t work with Access data, it is dealt with in the chapters on working with text files, specifically Chapters 9 and 17.