FIX: Access ODBC Keyset Cursor Becomes Corrupt After a Delete

This article has been archived. It is offered "as is" and will no longer be updated.
After deleting a row and doing a MoveNext or MovePrevious, the current record is wrong.

Exhibited behavior indicates that the current record will either become another record from within the recordset or EOF depending on the size of the recordset.
A bug was introduced in MDAC 2.1 affecting the behavior of the Access ODBC Driver keyset cursors.
To resolve this problem, upgrade to MDAC 2.1 Service Pack 2 or MDAC 2.5 or later. These versions of MDAC can be obtained from the download section at the following Microsoft Web site:
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

This problem was corrected in MDAC 2.1 SP2.
More information
The specific MDAC 2.1 DLL causing the problem is odbcjt32.dll.

Version 4.0.3513.00 shipped with MDAC 2.1 that was included withSQL Server 7.0.

Version 4.0.3711.08 of odbcjt32.dll shipped with MDAC 2.1 SP1.

MDAC 2.1 SP1 has shipped with Office 2000 and is downloadable at the MDAC Web site.

The following Visual C++ code in conjunction with the Microsoft sample Northwinddatabase can be used to illustrate the behavior. It assumes that you used theMFC AppWizard to generate a CRecordset-derived class that wraps the OrderDetails table in nwind.mdb. The recordset must be opened as aCRecordset::dynaset to reproduce the problem:
   CDBwindSet rs;   rs.Open();   rs.m_pDatabase->BeginTrans();   rs.MoveNext();   rs.Delete();   rs.MoveNext();  //You are now on the wrong record   rs.m_pDatabase->Rollback();   rs.Close();				
The following Visual Basic code in conjunction with the Microsoft sampleNorthwind database can be used to illustrate the behavior. The cursor type needsto be adOpenDynamic. ADO will degrade to a keyset cursor because the Access ODBCdriver does not support dynamic cursors.

It appears that setting the ADO recordset property CacheSize to something otherthan 1 (the default) causes the bug to not appear:
   Dim cnNorthwind As ADODB.Connection    Dim rsOrderDetails As ADODB.Recordset   Private Sub Form_Load()       Dim strConn As String       Dim strSQL As String              strConn = "Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\temp\NWind.MDB;"       strSQL = "SELECT * FROM [Order Details] ORDER BY OrderID, ProductID"              Set cnNorthwind = New ADODB.Connection       cnNorthwind.Open strConn       cnNorthwind.BeginTrans              Set rsOrderDetails = New ADODB.Recordset       rsOrderDetails.Open strSQL, cnNorthwind, adOpenDynamic, adLockOptimistic, adCmdText              rsOrderDetails.MoveNext       MsgBox "Row 2 = " & rsOrderDetails!OrderID & " - " & rsOrderDetails!ProductID       rsOrderDetails.MoveFirst       rsOrderDetails.Delete       rsOrderDetails.MoveNext       'Note you expect to be on Row 2 as before but you are not       MsgBox "Row 2 = " & rsOrderDetails!OrderID & " - " & rsOrderDetails!ProductID       rsOrderDetails.Close       cnNorthwind.RollbackTrans       cnNorthwind.Close   End Sub				

Article ID: 230131 - Last Review: 01/10/2015 11:56:59 - Revision: 7.0

  • Microsoft Data Access Components 2.1
  • kbnosurvey kbarchive kbhotfixserver kbqfe kbbug kbfix kbjet kbmdac210sp2fix kbmdac250fix KB230131