FIX: ADO Client Cursors Report LockType = adLockPessimistic

ID: Q190625

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SYMPTOMS

ActiveX Data Objects (ADO) incorrectly reports that a client-side recordset opened with a LockType of adLockPessimistic was opened with pessimistic locking.

The ADO client cursor engine does not support pessimistic locking. If you ask ADO for a pessimistically locked client-side recordset, you actually receive an optimistically locked recordset instead, even though ADO did not change the value of the LockType property to adLockOptimistic.

With MDAC 2.1 sp2 the locktype is now returned as adlockbatchoptimistic(4).

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

This bug has been fixed in MDAC 2.1 Service Pack 2.

MORE INFORMATION

Steps to Reproduce Behavior

Open a Standard.exe project in Visual Basic, and add a reference to the Microsoft ActiveX Data Object Library.

Copy and paste the following code into the form code window:


      Option Explicit
      Dim myconn As New ADODB.Connection
      Dim myrs As New ADODB.Recordset

      Private Sub Form_Load()
        Dim strConn As String

        'Modify the connection string to point to your NWind.MDB file.
         strConn = "Provider=MSDASQL;" & _
              "Driver={Microsoft Access Driver (*.mdb)};" &; _
              "DBQ=D:\vb5\Labs\LAB10\NWind.MDB"

        myconn.CursorLocation = adUseClient
         myconn.Open strConn

       'Request a pessimistically locked cursor.
       'ADO's client cursor engine only supports optimistic locking.
         myrs.Open "SELECT * FROM Customers", myconn, _
              adOpenStatic, adLockPessimistic, adCmdText
         MsgBox "MyRS.LockType = " & myrs.LockType &amp; vbCrLf & _
                "adLockPessimistic = " & adLockPessimistic & vbCrLf & _
                "adLockOptimistic = " & adLockOptimistic
         myrs.Close

Run the form and note the output of the message boxes.

REFERENCES

ADO 2.0 documentation

Additional query words:

Keywords : kbADO150bug kbADO200 kbADO200bug kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2fix kbMDAC210SP2fix
Version : WINDOWS:1.5,2.0,2.1,5.0,6.0
Platform : WINDOWS
Issue type : kbbug