Microsoft KB Archive/248186

= 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).



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: <pre class="fixed_text">  INSERT INTO TBLTEST VALUES('MB021', '100', 5, '# SITE COUNT-DEALERS', 0, 0, 0); </li> In the ODBC Administrator, create a DSN to the Oracle server with the Oracle ODBC Driver.</li> Start Access, and then create a new database.</li> On the File menu, point to Get External Data, and then click Link Tables.</li> In the Files of type list, click ODBC Databases.</li> In the Select Data Sources dialog box, select the DSN that you created in step 4, and then link to the Oracle table TBLTEST.</li> Open the linked table. Note that #Deleted appears in each field of the record.</li></ol>

<div class="references_section">