Microsoft KB Archive/253561

= 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
 In Microsoft Visual Basic 5.0 or 6.0, create a new Standard EXE project. Form1 is created by default. On the Project menu, select References, and then add the following type library:

Microsoft ActiveX Data Objects 2.1 Library

  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.

  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 </li></ol>

Additional query words: Access VBA

Keywords: kbbug kbfix kbqfe kbjet kbhotfixserver KB253561

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.