"#Deleted" errors with linked ODBC tables (128809)
The information in this article applies to:
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q128809 SYMPTOMS When you retrieve, insert, or update records in a linked
ODBC table, each field in a record contains the "#Deleted" error message. When
you retrieve, insert, or update records using code, you receive the error
message "Record is deleted." CAUSE The Microsoft Jet database engine is designed around a
keyset-driven model. This means that data is retrieved, inserted, and updated
based on key values (in the case of a linked ODBC table, the unique index of a
table). After Microsoft Access performs an insert or an update of a
linked ODBC table, it uses a Where criteria to select the record again to
verify the insert or update. The Where criteria is based on the unique index.
Although numerous factors can cause the select not to return any records, most
often the cause is that the key value Microsoft Access has cached is not the
same as the actual key value on the ODBC table. Other possible causes are as
follows: - Having an update or insert trigger on the table, modifying
the key value.
- Basing the unique index on a float value.
- Using a fixed-length text field that may be padded on the
server with the correct amount of spaces.
- Having a linked ODBC table containing Null values in any of
the fields making up the unique index.
These factors do not directly cause the "#Deleted" error
message. Instead, they cause Microsoft Access to go to the next step in
maintaining the key values, which is to select the record again, this time with
the criteria based on all the other fields in the record. If this step returns
more than one record, Microsoft Access returns the "#Deleted" message because
it does not have a reliable key value to work with. If you close and re-open
the table or choose Show All Records from the Records menu, the "#Deleted"
errors are removed. Microsoft Access uses a similar process to
retrieve records from an linked ODBC table. First, it retrieves the key values
and then the rest of the fields that match the key values. If Microsoft Access
is not able to find that value again when it tries to find the rest of the
record, it assumes that the record is deleted. RESOLUTION The following are some strategies that you can use to avoid
this behavior: - Avoid entering records that are exactly the same except for
the unique index.
- Avoid an update that triggers updates of both the unique
index and another field.
- Do not use a Float field as a unique index or as part of a
unique index because of the inherent rounding problems of this data type.
- Do all the updates and inserts by using SQL pass-through
queries so that you know exactly what is sent to the ODBC data source.
- Retrieve records with an SQL pass-through query. An SQL
pass-through query is not updateable, and therefore does not cause "#Delete"
errors.
- Avoid storing Null values within any field making up the
unique index of your linked ODBC table.
Modification Type: | Major | Last Reviewed: | 1/25/2006 |
---|
Keywords: | kberrmsg kbinterop kbprb KB128809 |
---|
|