Microsoft KB Archive/253561
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 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
Additional query words: Access VBA
Keywords: kbbug kbfix kbqfe kbjet kbhotfixserver KB253561