Re: #Deleted message showing in all fields on an access form

In <5a4tn1$5b2_at_nr1.vancouver.istar.net> "Trevor Hughes" <bambi_at_southern.co.nz> writes:
>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 (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. 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, ODBC, SQL Net don't matter...

Anybody got a solution?

Thanks in advance

        Martin

-- 
               Martin Schr"oder, MS_at_Dream.HB.North.DE
Also an Frauen nasche ich zu gerne rum (die sind auch viel kalorien-
        "armer als der Unsinn aus der Konditorei). (Noses)