Microsoft KB Archive/253561

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 12:52, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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