|
|
|
|
|
I'm getting #deleted entries in
resultsets from ODBC since installing SQL 7. What is going on? Several possibilities - 1. Could be a bug with numeric/decimal fields that only occurs with ODBC driver 3.70.0623. Upgrade to 3.70.0690 which comes with SQL 7 SP1 and/or MDAC 2.1 SP2. 2. PSS ID Number: Q128809 Article last modified on 01-06-1998 2.0 WINDOWS ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Access version 2.0, 7.0, 97 --------------------------------------------------------------------- SYMPTOMS ======== When you retrieve, insert, or update records in an attached 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 an attached ODBC table, the unique index of a table). After Microsoft Access performs an insert or an update of an attached 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 an attached 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 attached 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 attached ODBC table. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- 1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0) 2. Use the Upsizing Tools to upsize the Shippers table. NOTE: This table contains an AutoNumber field (or Counter field in Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools into a trigger that emulates a counter. 3. Open the attached Shippers table and enter a new record. Make sure that the record you enter has the same data in the Company Name field as the previous record. 4. Press TAB to move to a new record. Note that the "#Deleted" error fills the record you entered. 5. Close and re-open the table. Note that the record is correct. |
All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.