Microsoft KB Archive/253561

From BetaArchive Wiki

Article ID: 253561

Article Last Modified on 9/26/2005


  • Microsoft OLE DB Provider for Jet 4.0

This article was previously published under Q253561


When deleting records in a loop using the Microsoft Jet 4.0 OLE DB provider, not all records get deleted from the table.


When using forward-only, server-side cursors, the Microsoft Jet 4.0 OLE DB provider incorrectly positions the cursor after executing the Delete method.


To resolve this problem, do one of the following:

  • Use a different cursor type, such as Keyset.
  • Use a SQL statement, such as: DELETE * FROM MyTable.


This has been fixed in Microsoft Jet 4.0 SP4.


The problem manifests itself only when using forward-only, server-side cursors and deleting records in a loop similar to this:

Do While Not rs.EOF

With a 10-record table:

  • With the Microsoft Jet 3.51 OLE DB provider (MSJTOR35.DLL 3.52.1527.4), the loop gets executed 10 times.
  • With the Microsoft Jet 4.0 OLE DB provider from MDAC 2.1 GA, the loop gets executed five times.
  • With the Microsoft Jet 4.0 OLE DB provider from MDAC 2.1 SP2 (JETOLEDB40.DLL 4.00.2927.2), the loop gets executed twice.
  • With the Microsoft Jet 4.0 OLE DB provider (JETOLEDB40.DLL 4.00.3714.0), the loop gets executed 10 times, as expected.

Steps to Reproduce Behavior

  1. In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default.
  2. On the Project menu, select References, and then add the following type library:

    Microsoft ActiveX Data Objects 2.1 Library

  3. Add a Command button (Command1) and the following code to the default form:

    Option Explicit
    Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, I As Long
      Set cn = New ADODB.Connection
      cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
      cn.Execute "CREATE TABLE DB12374 (ID Int)"
      For I = 1 To 10
        cn.Execute "INSERT INTO DB12374 VALUES (" & I & ")"
      Next I
      Set rs = New ADODB.Recordset
      rs.Open "SELECT * FROM DB12374", cn, adOpenForwardOnly, adLockOptimistic, adCmdText
      I = 0
      Do While Not rs.EOF
        I = I + 1
        Debug.Print I, "Deleting"; rs(0)
      cn.Execute "DROP TABLE DB12374"
    End Sub

    NOTE: You might need to adjust the connect string to point to a valid Jet database.

  4. Execute the project. If you have an older version of the provider, you should get output similar to the following:

     1            Deleting 1 
     2            Deleting 2 

    If you have the latest version of the provider, or change the cursor type from adOpenForwardOnly to adOpenKeyset, the output should be:

     1            Deleting 1 
     2            Deleting 2 
     3            Deleting 3 
     4            Deleting 4 
     5            Deleting 5 
     6            Deleting 6 
     7            Deleting 7 
     8            Deleting 8 
     9            Deleting 9 
     10           Deleting 10 

Additional query words: Access VBA

Keywords: kbbug kbfix kbqfe kbjet kbhotfixserver KB253561