PRB: Space in ConnectString Does Not Allow Pessimistic Locking

Placing a space in the ConnectString after the keyword Provider will not allow the use of pessimistic locking (adLockPessimistic).

Remove the space from the ConnectString.

This behavior is by design.

This only happens using the SQLOLEDB, Jet 3.51 and Jet 4.0 OLEDB Providers with Server-Side cursors and Pessimistic Locking.

Steps to Reproduce Behavior

1. Start a new Visual Basic Standard EXE project. Form1 is added by default.
2. From the Project menu, click References, and select the Microsoft ActiveX Data Objects.
3. Place the following code in the Code Window of Form1.

   Private Sub Form_Load()
   
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   
   set cn = New ADODB.Connection
   set rs = New ADODB.Recordset
   
   cn.Open "Provider =Microsoft.jet.oledb.3.51;Data Source=c:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"    'Causes Problem
   'cn.open  "Provider=Microsoft.jet.oledb.3.51;Data Source=c:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"    'Does not cause the problem
   
   With rs
       .CursorLocation = adUseServer
       .CursorType = adOpenKeyset
       .LockType = adLockPessimistic
       .Open "Select * from Customers", cn
   End With
   
   Debug.Print rs.CursorLocation    'Should be 2
   Debug.Print rs.CursorType        'Should be 1
   Debug.Print rs.LockType          'Should be 2 but you get 4
    
   End Sub
   					

4. Run the Project.

Result: You receive a BatchOptimistic (4) instead of a Pessimistic lock (2).