ACC: ORACLE 7.0 CHAR Unique Index Causes "Record Deleted" (103177)
The information in this article applies to:
- Microsoft Access 1.0
- Microsoft Access 1.1
- Microsoft Access 2.0
This article was previously published under Q103177 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 INFORMATIONSteps 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).
| Modification Type: | Major | Last Reviewed: | 5/6/2003 |
|---|
| Keywords: | kbprb kbusage KB103177 |
|---|
|