Microsoft KB Archive/248186

Knowledge Base

ACC97: #Deleted Appears in Linked Oracle Tables in Access Service Release 2 (SR-2)

Article ID: 248186

Article Last Modified on 1/27/2007

---

APPLIES TO

• Microsoft Access 97 Standard Edition

---

This article was previously published under Q248186

Advanced: Requires expert coding, interoperability, and multiuser skills.

SYMPTOMS

In an Access database, when you open a table in Datasheet view, and the table is a linked Oracle table to which you have linked with the Oracle ODBC driver from Oracle Corporation, you see #Deleted in all fields.

CAUSE

You are using Microsoft Access 97 Service Release 2 (SR-2), and the linked table has a multiple field index that includes a column data type of char, or varchar2, followed by a column of data type number. In this case, it does not matter if this index was created on the server or was specified when the linked table was created.

NOTE: This issue occurs only in Access 97 SR-2 and Access 2000.

RESOLUTION

Base the data source on the Microsoft ODBC for Oracle driver, which is included with the Microsoft Data Access Components (MDAC). To download the latest version of MDAC, visit the Universal Data Access Web site, which is located at the following Microsoft Web address:

http://msdn.microsoft.com/dataaccess

STATUS

This is a known issue in Microsoft Access 97 Service Release 2 (SR-2).

MORE INFORMATION

Steps to Reproduce the Behavior

1. With the Oracle client tools, create a table in an Oracle database by running the following script:

      CREATE TABLE TBLTEST
      (
      ROLLUP VARCHAR2(7) NOT NULL,
      COT VARCHAR2(4) NOT NULL,
      LINE NUMBER(7,1) NOT NULL,
      LINE_DESCRIPTION VARCHAR2(55) NULL,
      CALC_LINE NUMBER(7,1) NULL,
      INDENT NUMBER(1) NULL,
      M01 NUMBER(14,2) NULL
      )
                       

2. Create an index on the table with the following script:

      CREATE UNIQUE INDEX IND1
      ON TBLTEST(ROLLUP ASC,COT ASC,LINE ASC)
                       

3. Insert a record into the table with the following script:

      INSERT INTO TBLTEST VALUES('MB021', '100', 5, '# SITE COUNT-DEALERS', 0, 0, 0);
                       

4. In the ODBC Administrator, create a DSN to the Oracle server with the Oracle ODBC Driver.
5. Start Access, and then create a new database.
6. On the File menu, point to Get External Data, and then click Link Tables.
7. In the Files of type list, click ODBC Databases.
8. In the Select Data Sources dialog box, select the DSN that you created in step 4, and then link to the Oracle table TBLTEST.
9. Open the linked table. Note that #Deleted appears in each field of the record.

REFERENCES

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Additional query words: pra pounddeleted pound deleted numbersign number sign numbersigndeleted

Keywords: kbbug KB248186

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.