#deleted on insert with Oracle and Access 2000/XP/2003

[Guest]

Hi All,

I have a bizarre scenario the causes Access to produce #Deleted record on an
insert.

Oracle (Oracle 8.1, ODBC driver 8.01) table with trigger that produces
sequential index for PK.
Table (SCOTT.EMP) linked to any version of Access later than 97. Create new
record via query/form. However, depending on the value of another field
validation sets a non-indexed field (JOB) to Null (also tried ""). This
causes the record to be created, but the form displays #Deleted in its
fields.

Having done an ODBC trace, it appears that it is trying to find the record
from the data that has been entered. However, unlike 97, the way ODBC treats
the Null is more like an empty string which results in no record found. If
JOB has a value it works fine.

Access 2000 trace

MSACCESS 810-2e0 ENTER SQLExecDirectW
HSTMT 07C22C90
WCHAR * 0x118751D8 [ -3] "SELECT "SCOTT"."EMP"."EMPNO"
FROM "SCOTT"."EMP" WHERE "ENAME" = ? AND "JOB" = ?\ 0"
SDWORD -3

I have contacted our Oracle support and after much debate, they are saying
it is Access/Jet that has changed, not their problem!

Any help much appreciated

Cheers
Gordon


Oralce 8.1, ODBC driver 8.01

 

[Albert D. Kallal]

I would ensure that the query (or table) exposes a time stamp in the query.

If you have a timestamp field in the data..then ms-access can figure out
things a lot easer.