CursorType, LockType, and EditMode Properties Example (VB)

This example demonstrates setting the CursorType and LockType properties before opening a Recordset. It also shows the value of the EditMode property under various conditions. The EditModeOutput function is required for this procedure to run.

'BeginEditModeVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub Main()
    On Error GoTo ErrorHandler

    ' recordset variables
    Dim rstEmployees As ADODB.Recordset
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim SQLEmployees As String
    
     ' open connection
    Set Cnxn = New ADODB.Connection
    strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    Cnxn.Open strCnxn
       
      ' set recordset properties through object refs
      ' instead of through arguments to Open method
    Set rstEmployees = New ADODB.Recordset
    Set rstEmployees.ActiveConnection = Cnxn
    rstEmployees.CursorLocation = adUseClient
    rstEmployees.CursorType = adOpenStatic
    rstEmployees.LockType = adLockBatchOptimistic
    
     ' open recordset with data from Employee table
    SQLEmployees = "employee"
    rstEmployees.Open SQLEmployees, , , , adCmdTable
    
    ' Show the EditMode property under different editing states
    rstEmployees.AddNew
        rstEmployees!emp_id = "T-T55555M"
        rstEmployees!fname = "temp_fname"
        rstEmployees!lname = "temp_lname"
            'call function below
            'to output results to debug window
        EditModeOutput "After AddNew:", rstEmployees.EditMode
        rstEmployees.UpdateBatch
        EditModeOutput "After UpdateBatch:", rstEmployees.EditMode
        rstEmployees!fname = "test"
        EditModeOutput "After Edit:", rstEmployees.EditMode
    rstEmployees.Close
    
    ' Delete new record because this is a demonstration
    Cnxn.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"

    ' clean up
    Cnxn.Close
    Set rstEmployees = Nothing
    Set Cnxn = Nothing
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstEmployees Is Nothing Then
        If rstEmployees.State = adStateOpen Then rstEmployees.Close
    End If
    Set rstEmployees = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If

End Sub

Public Function EditModeOutput(strTemp As String, _
   intEditMode As Integer)

   ' Print report based on the value of the EditMode
   ' property
   Debug.Print strTemp
   Debug.Print "  EditMode = ";

   Select Case intEditMode
      Case adEditNone
         Debug.Print "adEditNone"
      Case adEditInProgress
         Debug.Print "adEditInProgress"
      Case adEditAdd
         Debug.Print "adEditAdd"
   End Select

End Function
'EndEditModeVB

See Also

CursorType Property | CursorTypeEnum | EditMode Property | EditModeEnum | LockType Property | LockTypeEnum | Recordset Object