Microsoft KB Archive/253561

Knowledge Base

FIX: Delete/MoveNext Moves to Wrong Record with Jet 4.0 Provider

Article ID: 253561

Article Last Modified on 9/26/2005

---

APPLIES TO

• Microsoft OLE DB Provider for Jet 4.0

---

This article was previously published under Q253561

SYMPTOMS

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

CAUSE

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

RESOLUTION

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.

STATUS

This has been fixed in Microsoft Jet 4.0 SP4.

MORE INFORMATION

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
   rs.Delete
   rs.MoveNext
Loop
                

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)
       rs.Delete
       rs.MoveNext
     Loop
     rs.Close
     cn.Execute "DROP TABLE DB12374"
     cn.Close
   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

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.