Symptoms
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.
Resolution
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 valuethat it is storing, it raises an error.Triggers. Triggers can change the values in the indexed fields. Sincea trigger changes the values from what the Jet engine thinks it putin there, the cached keyset value and the actual value in the tablediffer. When the Jet engine tries to fetch the record, it will raisean error.Null and Empty String behavior. Many databases automatically changedata if necessary without alerting the Jet engine. For example, if theuser were to add a record where the indexed column was a varchar() andthe user submitted a “” value for that field, SQL Server would changethe “” into a space (” “) and not alert the Jet engine. The Jet enginewould 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 asGetDate(). These can change the indexed data without the Jet engineknowing it.Indexes on non-standard datatypes or floating point datatypes. Someserver datatypes have no ODBC or Jet engine equivalent datatype. In mostcases, the Recordset is created as read-only, but sometimes it is not.Rounding or conversion errors on the datatypes can cause the error aswell.
