ACC: ORACLE 7.0 CHAR Unique Index Causes "Record Deleted"

 Last reviewed: May 28, 1997
Article ID: Q103177
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0

SYMPTOMS

If you are using an ORACLE version 7.0 database in Microsoft Access, the message "Record deleted" will appear when you enter a record with a unique index (or primary key) based on a CHAR data type.

RESOLUTION

Change the data type for the field in ORACLE 7.0 from CHAR to VARCHAR, which will allow a variable-length data field.

CAUSE

In ORACLE version 7.0, if you define a field with a data type of CHAR(12), the data in that field is always stored as 12 characters; ORACLE will pad the data with blank characters if necessary. If the index is built on such a field, a problem will occur since Microsoft Access stores the value of the unique index and uses this to keep track of the records in the ORACLE table.

Because the value that is actually being stored has been altered by ORACLE (due to the added padding), Microsoft Access does not find a match and assumes another user deleted the record. A requery will clear up the immediate problem until another record is entered. When a requery is issued, Microsoft Access rereads the index values and will find the entry (including the padded characters).

MORE INFORMATION

Steps to Reproduce Behavior

If you define a unique index field with a data type of CHAR(10) in an ORACLE 7.0 database, attach that table to a Microsoft Access database, and enter a record in which the value for the unique index is 7 characters (for example, "ACME CO"), Microsoft Access will assume the record has been deleted. This is because the key is saved as "ACME CO " (10 characters) rather than "ACME CO" (7 characters).

Keywords : kbusage OdbcOthr
Version : 1.0 1.1 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.