#Deleted is displayed in the records when you open a linked ODBC table from an Oracle 10g database in Access 2003, in Access 2002, or in Access 2000 (913070)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
  • Microsoft Access 2000

SUMMARY

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.

SYMPTOMS

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 linked ODBC table uses the VARCHAR2 data type for the primary key.
  • The Oracle client character set that is stored in the NLS_LANG setting differs from the Oracle database character set that is stored in the NLS_CHARACTERSET setting.
  • You are using a version of the Oracle ODBC driver that is earlier than version 10.1.0.4.

CAUSE

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.

RESOLUTION

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 set

Change 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:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1

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 set

Use 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:
  1. Start Notepad.
  2. In a text file, type the following commands:

    set NLS_LANG=LANGUAGE

    start "program path\msaccess.exe" "database path"

    Note In the first command, LANGUAGE is a placeholder for the fully qualified Oracle language name for the character set that is used on the server. For example, type AMERICAN_AMERICA.WE8MSWIN1252. In the second command, program path is a placeholder for the full path of the Msaccess.exe file. For example, type C:\Program Files\Microsoft Office\OFFICE11. In the second command, database path is a placeholder for the full path of the Access database file that you want to open. For example, type C:\Databases\My Database.mdb.
  3. Save the file.

    Important Make sure that you save the file by using the .cmd extension.
  4. To start Access and to open the database file, double-click the .cmd file.

Method 3: Install the updated Oracle ODBC driver version 10.1.0.4

Install 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.

WORKAROUND

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 Oracle

Instead 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 it

Instead 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:
  1. Start Access.
  2. Open the database that contains the linked ODBC table.
  3. Create a form or a query.
  4. Open the form or the query in Design view.
  5. For a form, follow these steps:
    1. To open the Form Properties dialog box, click Properties on the View menu.
    2. In the Form Properties dialog box, click Form, and then click the Data tab.
    For a query, follow these steps:
    1. To open the Query Properties dialog box, click Properties on the View menu.
    2. In the Query Properties dialog box, click the General tab.
  6. Under Recordset Type, click Snapshot.

Method 3: Use an SQL pass-through query

Instead 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:

303968 How to create an SQL pass-through query in Access

MORE INFORMATION

Steps to reproduce the problem

  1. In Oracle, create a database table that uses the VARCHAR2 data type for the primary key.
  2. On a clean computer, install Microsoft Office 2003, Microsoft Office XP, or Microsoft Office 2000.
  3. Install the Oracle 10g client application.
  4. Confirm that the Oracle client character set that is stored in the NLS_LANG setting differs from the Oracle database character set that is stored in the NLS_CHARACTERSET setting.

    Note The NLS_LANG setting is stored in the following registry subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1

    For more information about how to determine the Oracle database character set that is stored in the NLS_CHARACTERSET setting, see the Oracle product documentation.
  5. By using ODBC Administrator, create a new DSN for the Oracle 10g database that uses the Oracle in OraClient10g_home1 driver.
  6. Start Access.
  7. On the File menu, click New.
  8. Under New File, click Blank database.
  9. Type a name for the database, and then click Create.
  10. On the File menu, point to Get External Data, and then click Link Tables.
  11. In the Link dialog box, change the Files of type box to ODBC Databases.
  12. In the Select Data Source dialog box, click Machine Data Source.
  13. Select the DSN that you created in step 5, and then click OK.
  14. Enter the appropriate logon information, and then click OK.
  15. Find and select the database table that you created in step 1, and then click OK.
  16. Open the database table in Datasheet view. Notice that #Deleted is displayed for the values in the records.
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.
Modification Type:MajorLast Reviewed:3/30/2006
Keywords:kbOracle kbExpertiseInter kbtshoot kbprb KB913070 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.