Troubleshooting
Problem
This document lists some of the common causes of Microsoft Access "#deleted" errors encounted when using the iSeries Access for Windows ODBC driver.
Resolving The Problem
|
Caution: The information in this document is provided on an as-is basis. #deleted is a Microsoft Access application error message that can have many underlying causes. Rochester Support Center supports Client Access ODBC and IBM® DB2®/400 but not Microsoft Access. The following is a list of errors encountered by the Rochester Support Center. Please contact Microsoft for additional information. |
Microsoft Access may invoke the ODBC cursor library when using Client Access ODBC. The typical behavior is to query the unique keys on the table (SQLStatistics), select the unique key fields and fetch all rows to the PC, then select records as needed by specifying the unique key on the where clause. Access seems to pop up the #deleted error message when a select is done by unique key but no record is retrieved.
OS/400-Related Causes
| 1. | The row has been deleted by another user. |
| 2. | The table contains a column of the Open data type (mixed SBCS/DBCS CCSID) and the row contains DBCS data with redundant shift-in shift-out characters. |
| 3. | Numeric(zoned) or decimal conversion errors. If Access binds the numeric fields as character data the following apars can cause some records to show #deleted. Client Access Express ODBC APARs SA93266 (V4R5), SE04020 (V5R1). |
| 4. | The IBM® OS/400® or IBM® i5/OS™ system catalog tables are corrupted or did not return the proper fields contained in the unique key. This error is rare (it is usually seen only after a power loss or a failed system upgrade). Run the SQLStatistics API to confirm that the proper data is returned. SQLStatistics can be run from the ODBC SDK test program or the ODBC API sample program available from IBM support. |
| 5. | The index on the table or file is corrupted. This problem is also very rare but occasionally you may need to force a rebuild of the index. |
| 1. | Microsoft support lists many common causes in the following support document: Article ID: Q128809 ACC: "#Deleted" Errors with Linked ODBC Tables |
| 2. | Microsoft Access or Jet software bug/code level mismatch. Download the latest service pack for Microsoft Access. Install or reinstall the latest MDAC update and Jet component update. The updates are available at support.microsoft.com/?scid=ph;en-us;1595. MDAC 2.6 and later no longer include the Jet components. They must be installed separately. |
| 3. | Query time-out value has expired. The jet engine will typically set a time-out value on all queries. Adjust the timeout value used by Access or disable query timeout support. |
| 4. | CCSID 65535 character fields (SQL binary). DB2/400 and Client Access ODBC treat CCSID 65535 fields as binary. Some versions of Microsoft Access do not handle binary data types. If the field is incorrectly defined as CCSID 65535 then re-define the field or set the Client Access ODBC datasource option to force 65535 character translation. Check the "Convert binary data (CCSID 65535) to text" option located on the Translation tab of the datasource. |
| 5. | Microsoft Access truncates Time and Timestamp fields. For example, timestamp fields with non-zero microsecond precision will appear as #deleted. Access also does not support the same range of values for date. Dates for years below 100 (such as the common default value of 0001-01-01) may appear as #deleted. Refer to Rochester Support Center Technote N1018336, SQL0180 Syntax of Date, Time, or Timestamp Not Valid for further information. There may be other field types where the OS/400 or i5/OS system precision or scale exceeds the Microsoft Access data type. This may result in a similar error. |
| 6. | The table being linked my have a numeric key column with greater scale than Microsoft Access can represent. For example, if a table with a primary key that is a bigint type will cause all the data to be displayed as #deleted. To circumvent this, create a view over the table, and link the view without selecting a key column. The table will be read-only; however, you will be able to view the data. |
[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0"}]
Historical Number
13831159
Was this topic helpful?
Document Information
Modified date:
18 December 2019
UID
nas8N1019663