PRB: Explaining "Record is deleted" error accessing ODBC table

This article was previously published under Q172339
This article has been archived. It is offered "as is" and will no longer be updated.
Error 3167 "Record is Deleted" is a common error when using the Data AccessObjects (DAO) or a data control to access ODBC tables. This is due to theway that the Microsoft Jet Database Engine manipulates its cursor for therecordset. It is not limited to DAO. Similar errors can be raised by anyengine that maintains a cursor. The ODBC cursor library and serversthemselves can and will raise similar errors. Understanding why and howthese errors are caused requires a knowledge of resultset and cursorbehavior.
The "Record is Deleted" error (error 3167) is a byproduct of the Jetengine's keyset cursor for the dynaset type recordset. A keyset cursor isfixed in membership, but there is nothing stopping another user fromdeleting a row in the underlying table that you have selected in yourkeyset. When you attempt to get the data or update the data in a deletedrow, the "Record is Deleted" error message is generated. Again, thisbehavior is not limited to the Jet engine, but can occur in any keysetcursor.

This is not the only cause of the error. There are several other causesthat are far more subtle and depend on the keyset implementation. Becausethe Jet engine uses a keyset based on a unique index in the underlyingtables, it is possible to get this error if something changes the indexinformation. When the fields that the keyset is built from are changed inthe underlying table for a given record, the Jet engine is not able to findthe record to read or update the data and raises the "Record is Deleted"error. In most cases the Jet engine knows that the indexed fields changedin the underlying table if it made the changes, but the following couldchange the indexed fields without the Jet engine being aware of it:
  • Other users. Other users may change the values in the indexed fields. When the Jet engine is unable to find that record based on the value that it is storing, it raises an error.
  • Triggers. Triggers can change the values in the indexed fields. Since a trigger changes the values from what the Jet engine thinks it put in there, the cached keyset value and the actual value in the table differ. When the Jet engine tries to fetch the record, it will raise an error.
  • Null and Empty String behavior. Many databases automatically change data if necessary without alerting the Jet engine. For example, if the user were to add a record where the indexed column was a varchar() and the user submitted a "" value for that field, SQL Server would change the "" into a space (" ") and not alert the Jet engine. The Jet engine would then try to find that record with "", fail to do so and raise the "Record is Deleted" error.
  • Functions. Many indexed fields are updated with server functions such as GetDate(). These can change the indexed data without the Jet engine knowing it.
  • Indexes on non-standard datatypes or floating point datatypes. Some server datatypes have no ODBC or Jet engine equivalent datatype. In most cases, the Recordset is created as read-only, but sometimes it is not. Rounding or conversion errors on the datatypes can cause the error as well.
Proper table structure and recordset creation is all that is usuallyneeded. Where this is not possible, there are a few alternatives:
  • Use SQL statements to do the updating and deleting and use read-only snapshot type recordsets to view the data. Refresh the recordset as needed.
  • Refresh the recordset after every edit or addnew or execute operation that affects the data in the recordset. The Jet engine will rebuild the keyset with the updated information.
  • Remove triggers, functions, and so forth from the ODBC table and perform those actions manually.
This behavior is by design. Keyset cursors behave in this manner. Since theJet engine implements a keyset cursor, it is subject to the limitations ofthe cursor.
A resultset is the set of records obtained from an SQL query (generally aSELECT statement). A cursor is a way of maintaining position in aresultset, but is often thought of as the combination of the resultset andthe actual cursor.

Cursors (as they apply to ODBC and most database servers) are eitherForward Only or Scrollable. A Forward Only cursor is a very simple cursor.You can move forward only one row at a time. Scrollable cursors allow youto move back and forth through the cursor, optionally allowing for exactpositioning, determining position, finding records, and so forth.Scrollable cursors can optionally be Block cursors in that the cursor canfetch records a block at a time. The block of data is often referred to asa rowset. Cursors can also be Static, Dynamic, Keyset, or Mixed.

A Static cursor is a cursor in which membership, order, and values arefixed upon opening. The data appears to be static. It may change in theunderlying tables, but the cursor is unaware of the changes until it isrefreshed.

A Dynamic cursor is exactly the opposite. The membership, order and valuesare completely dynamic. The data reflects what is currently in theunderlying tables at that moment (usually limited by some refresh rate).

A Keyset cursor is a cursor that is fixed in membership and order, but notin values. A keyset cursor gets its name because a set of keys (bookmarks)that point to the data in the tables is created. Think of it as an array ofpointers to the actual data for each record in the tables.

A Mixed cursor is a mix of Dynamic and Keyset cursors. It is essentially acursor where the keyset does not contain all of the rows of the resultset(to save on memory). Therefore, there is a rowsetsize that is the size ofthe block of data fetched, a keysetsize that is the size of the keyset, anda resultsetsize that is the size of the entire resultset. The cursor ismixed because it is keyset within the current keyset, but dynamic outsideof the current keyset.

In regards to ODBC there are two classifications of cursors, Client-side(ODBC)and Server-side. Client-side cursors are maintained on the client'ssystem and Server-side cursors are maintained on the server. Client-sidecursors cannot realistically be dynamic.

Due to bandwidth limitations and other factors, client-side cursors aregenerally limited to Forward Only, Static, and Keyset. Server-side cursorscan be of any type.

The Jet engine creates and maintains its own cursors for ODBC recordsets.It does not rely on the ODBC cursor library cursors or server-side cursors.It does this so that it can provide the updatability of dynaset typerecordsets and allow SQL statements that span multiple Access, ISAM, orODBC databases. It provides the following cursors:
   Recordset Type       Cursor Type   -------------------- ---------------------------   Table                Dynamic (MDB and IISAM only; not available for                        ODBC)   SnapShot             Static read-only   Dynaset              Keyset				

A Note About Jet's Keyset Cursor Implementation

The Jet engine creates a keyset for the cursor based on a unique index onthe table. It queries the database for information on the table to find aunique index. If one is not found, a non-unique keyset cursor (dynaset typerecordset) will be built, but it will be read-only. This is because Jetupdates records via an UPDATE statement using the key fields to limit thechanges to a single record. If the table does not have a unique index, Jetcannot ensure that an update affects only a single record.

Other cursor libraries, like the ODBC cursor library, use moresophisticated updating schemes that can handle tables without indexes, butare more complex and error prone.

(c) Microsoft Corporation 1997, All Rights Reserved. Contributions byTroy Cambra, Microsoft Corporation
kbVBp500 kbVBp600 kbdse kbDSupport kbVBp kbRDO kbODBC kbVBp400

Article ID: 172339 - Last Review: 12/04/2015 17:35:07 - Revision: 4.1

Microsoft Visual Basic 4.0 Professional Edition, Microsoft Visual Basic 5.0 Professional Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 4.0 Enterprise Edition, Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Visual Basic 6.0 Enterprise Edition

  • kbnosurvey kbarchive kbprb KB172339