Help: #Deleted message showing in all fields of table

[Third try :-(]

In <5a4tn1$5b2_at_nr1.vancouver.istar.net> "Trevor Hughes" <bambi_at_southern.co.nz> w rites:
>Hello all
 

>I am cuurently in the process of upsizing a database from Access to Access
>as a front end and Sybase SQL Anywhere 5.0 as the datasource. This is the
>first time that I've done any work in Sybase before and I have come up
>against some problems.
 

>The main problem I'm encountering is that when I open one of my forms for
>data entry (main form subform opened in data entry mode due to the large
>number of records) all the fields have #DELETED in them. If I try to do
>anyting within the deleted fields I get a message saying that the record
>was deleted by another user (I am the only one currently using the
>database). I therefore cannot do anything with the record which shows
>#DELETED, however I can add new records.

We seem to have the same problem with an Oracle 7.1 dabatase. :-)

It is easily reproduced: Run this script as Scott: ----------- snipp -----------
create table MASTER (

   ID NUMBER(9),
   MASTER_NAME VARCHAR(30))
/
Create Sequence MASTER_ID_SEQ
  start with 1
  increment by 1
  nomaxvalue
  nocycle
  cache 20
/
create trigger MASTER_NEW

    before insert on MASTER FOR EACH ROW   Declare
    DUMMY number;
  Begin
    Select MASTER_ID_SEQ.NEXTVAL into DUMMY from DUAL;     :NEW.ID := DUMMY;
  End;
/
alter table MASTER

   ADD ( CONSTRAINT MASTER_PrimaryKey PRIMARY KEY (    ID)
     )
/
----------- snapp -----------
Attach the table to MS-Access (we run 2.x) and enter data in Table view. You should only enter the MASTER_NAME, the ID is generated automagically by Oracle (like a Counter in Access).

But if you enter the same MASTER_NAME twice in different records, the problem shows: When you tell Access to write the record (e.g. by hitting Return in Table view), Access shows #DELETED in all fields of the record. If you close the Table view and open it again, _then_ the data is there :-}

What seems to happen is that Access tells Oracle to write the record and _before_ the trigger has fired and Oracle has generated the new primary key, Access tries to read the newly written record. But since there is _now_ no primary key, it tries to identify it by MASTER_NAME -- and fails, since MASTER_NAME is not unique.
Looks like a race condition...

In one paper about ODBC in the KB is an entry which seems to describe this; it calls this "Volatile Primary Keys". They suggest Timestamps; but Oracle doesn't have Timestamps... :-(

We called Oracle support in Germany about this: they can reproduce it and didn't knew it -- they wanted to call Oracle US.

The versions of Access (2.0 or 2.5), ODBC, SQL Net don't seem to matter...

Anybody got a solution?

Thanks in advance

        Martib

-- 
               Martin Schr"oder, MS_at_Dream.HB.North.DE
Your eyes are weary from staring at the CRT for so long.  You feel
sleepy. Notice how restful it is to watch the cursor blink.  Close
your eyes. The opinions stated above are yours.  You cannot imagine
     why you ever felt otherwise. (manning_at_mars.jpl.nasa.gov)