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

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

MORE INFORMATION

Steps to Reproduce the Behavior

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
   )

Create an index on the table with the following script:

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

Insert a record into the table with the following script:

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

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