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

Back to the top

Do While Not rs.EOF
   rs.Delete
   rs.MoveNext
Loop
				

• 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.

Back to the top

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 
   					

Back to the top