Article ID: 913070 - View products that this article applies to.
When you open a linked Open Database Connectivity (ODBC) table from an Oracle 10g database in Microsoft Office Access 2003, in Microsoft Access 2002, or in Microsoft Access 2000, #Deleted is displayed in the records. The occurrence of this problem depends on several conditions. This article describes several methods to resolve or to work around this problem.
When you open a linked ODBC table from an Oracle 10g database in Access 2003, in Access 2002, or in Access 2000, #Deleted is displayed in the records. This problem occurs when all the following conditions are true:
The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on a key value. In the linked ODBC table, this key value is the unique index of the table.
Access uses a similar process to retrieve records from the linked ODBC table. First, Access retrieves the key value. Then, Access retrieves the rest of the fields that match the key value. If Access cannot find that key value again when Access tries to find the rest of the record, Access assumes that the record is deleted. When the Oracle client character set differs from the Oracle database character set, Access cannot find the key value, and #Deleted is displayed in the affected records.
To resolve this problem, use one of the following methods. Use the method that applies to your situation the best.
Method 1: Change the Oracle database character set to be the same as the Oracle client character setChange the Oracle database character set that is stored in the NLS_CHARACTERSET setting to be the same as the Oracle client character set that is stored in the NLS_LANG setting.
Note The NLS_LANG setting is stored in the following registry subkey:
For more information about how to change the Oracle database character set that is stored in the NLS_CHARACTERSET setting, see the Oracle product documentation.
Method 2: Dynamically change the Oracle client character set to be the same as the Oracle database character setUse a .cmd file to dynamically change the Oracle client character set to be the same as the Oracle database character set. To do this, follow these steps:
Method 3: Install the updated Oracle ODBC driver version 10.1.0.4Install the Oracle ODBC driver version 10.1.0.4. This driver is included in Oracle update 4493372. However, you must install client Oracle update 4163362 before you install Oracle update 4493372. To obtain these updates, contact Oracle.
Note This driver does not resolve an issue with the euro symbol and with 26 other characters that have a code between 128 and 159 in the WE8MSWIN1252 character set. This issue can only be resolved by using the same character set (WE8MSWIN1252) on the client computer and on the database.
If the methods in the "Resolution" section do not apply to your situation, use one of the following methods to work around this problem.
Method 1: Use the Microsoft ODBC Driver for OracleInstead of using the Oracle ODBC driver, you can use the Microsoft ODBC Driver for Oracle when you create an ODBC data source name (DSN).
Note This method does not work if the primary key contains characters that belong to one code page but not to another code page. For example, the euro symbol is part of the WE8MSWIN1252 character set, but the euro symbol is not part of the WE8ISO8859P1 character set. If a record has a primary key value that contains the euro symbol, the linked ODBC table displays #Deleted for every field value of this specific record. Other records are displayed correctly.
Method 2: Create the linked ODBC table, but do not open itInstead of opening the linked ODBC table directly, you can create a query, a form, or a report in Access that is based on the linked ODBC table. For a query or for a form, make sure that you click Snapshot under Recordset Type. Do not click Dynaset under Recordset Type. To do this, follow these steps:
Method 3: Use an SQL pass-through queryInstead of using the linked ODBC table, you can use an SQL pass-through query. By using an SQL pass-through query, you work directly with the server table instead of having the Microsoft Jet database engine process the data. For more information about how to create an SQL pass-through query in Access, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/303968/ )How to create an SQL pass-through query in Access
Steps to reproduce the problem