PRB: Use Open Method to Change CursorType and LockType

MORE INFORMATION

The default CursorLocation is adUseServer, and the default LockType is adLockReadOnly. The default CursorType for adUseServer is adOpenForwardOnly.

The CursorLocation can be set on the connection prior to opening the recordset. This CursorLocation is applied to the recordset opened on this connection. If the connection's CursorLocation is set to adUseClient, the resulting recordset CursorLocation would be adUseClient. The CursorType for adUseClient is always adOpenStatic. The default LockType remains adLockReadOnly.

The following sample code connects to SQL server's Pubs database. The ConnectionString arguments need to be changed to match your setup.

Steps to Reproduce Behavior

1. Create a Standard EXE project and select Microsoft Active Data Objects Library (on the Project menu, click References).
2. Place seven command buttons on Form1.
3. Paste the following code in the Form1 code window:

      Option Explicit
      Dim cn As New ADODB.Connection
      Dim cmd As New ADODB.Command
      Dim rs As New ADODB.Recordset
   
      Private Sub Command1_Click()
      With cn
         .CursorLocation = adUseServer
        .ConnectionString = "dsn=yourdatasourcename"uid=yourUserId;pwd=yourPassword
         .Open
      End With  
      rs.CursorType = adOpenKeyset
      rs.LockType = adLockOptimistic
      End Sub
   
      Private Sub Command2_Click()
         Set rs = cn.Execute("select * from authors")
      End Sub
   
      Private Sub Command3_Click()
        With cmd
          .ActiveConnection = cn
          .CommandType = adCmdText
          .CommandText = "select * from authors"
        End With
        Set rs = cmd.Execute
      End Sub
   
      Private Sub Command4_Click()
       rs.Open "select * from authors", cn
      End Sub
   
      Private Sub Command5_Click()
        Debug.Print "CursorLocation " & rs.CursorLocation; ""
        Debug.Print "cursortype " & rs.CursorType
        Debug.Print "locktype " & rs.LockType
      End Sub
   
      Private Sub Command6_Click()
        If rs.State = 1 Then
         rs.Close
        End If
        If Not rs Is Nothing Then
          Set rs = Nothing
        End If
        rs.CursorLocation = adUseServer
        rs.CursorType = adOpenKeyset
        rs.LockType = adLockOptimistic
        End Sub
   
      Private Sub Command7_Click()
        If Not rs Is Nothing Then
          Set rs = Nothing
        End If
        If cn.State = 1 Then
          cn.Close
        End If
        If Not cmd Is Nothing Then
       Set cmd = Nothing
        End If
        If Not cn Is Nothing Then
          Set cn = Nothing
        End If
        Unload Me
      End Sub
   
      Private Sub Form_Load()
        Command1.Caption = "connect"
        Command2.Caption = "connection.execute"
        Command3.Caption = "command.execute"
        Command4.Caption = "open recordset"
        Command5.Caption = "print recordset attributes"
        Command6.Caption = "close recordset"
        Command7.Caption = "quit"
      End Sub
   					

4. Run the code, and click Connect. Make sure the Immediate Window is open; if it is not open, select it from the View menu.
5. Click connection.execute; then select the print recordset attributes and close recordset. Note the print out is in the immediate window.
6. Click command.execute, followed by selecting print recordset attributes and close recordset. Note the print out is in the Immediate Window.
7. Click the open recordset followed by selecting print recordset attributes and the close recordset. Again, note the print out in the Immediate Window.

You may test the above sequences for the Connection, Command, and Open methods in any order, and the corresponding print outs will not change for Cursorlocation, Cursortype, and Locktype.

The following table lists the meanings of these numbers:

        Cursorlocation    Cursortype     Locktype

   ----------------------------------------------

   0    N/A              adforwardonly   N/A
   1    N/A              adopenkeyset    readonly
   2    aduseserver      adopendynamic   pessimistic
   3    aduseclient      adopenstatic    optimistic