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 TestKeysetOptimistic procedure 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 category 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$(3 9) & " already used; " _ & "please enter another category " _ & "name" strTitle = "Category used" MsgBox prompt:=strPrompt, _

Buttons:=vbExclamation + vbOKOnly, _ Title:=strTitle GoTo CategoryName Else

.AddNew

![Category] = strCategory .Update strPrompt = Chr$(39) & strCategory _

& Chr$(3 9) & " 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:

& "; 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 3 0 records in recordset after adding

0 0

Post a comment