Re: Help: #Deleted message showing in all fields of table

ms_at_dream.hb.north.de (Martin Schroeder) wrote:

>[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?

I had the same problem with an oracle database, used in an Access database using ODBC. The problem was fixed by changing any CHAR PKs to VARCHAR.

Now the records get inserted without the #deleted sign.

As much as I hate to advertise micro$oft... check out the knowledge base on www.microsoft.com, that's where I found this solution. Received on Fri Jan 10 1997 - 00:00:00 CST