Access with Oracle8 error : #Deleted instead of data

Access with Oracle8 error : #Deleted instead of data

Post by Abdullah Bushna » Sun, 24 May 1998 04:00:00



Hi folks,

we are using Access as the front end to develop our front end to a
Personal Oracle 8 Dbase running under NT Server 4.0.  We established the
ODBC connection, and we created linked tables to the Oracle tables and
we can access them without problems (well, most of the time).

A problem just showed up, one of our tables shows up with the word

        #Deleted

in place of the data that is supposed to be in the table.  The username
we are logging in as has full access to the Dbase, the table first
showed up without any problems, then the error showed up.  We removed
the data from the table, and tried things again, and at the first
attempt it worked, five minutes later, the problem showed up again (when
we accessed the tables again).

Now this is a pain in the neck.  If this is a problem that might show up
later, it would be a major problem.  Of course we couldn't get any help
from the Access help file.

If you have any suggestions or if you have faced this problem, I would
really appreciate any info.

Thanks,

Sincerely,

Abdullah Bushnaq

 
 
 

Access with Oracle8 error : #Deleted instead of data

Post by Kevin P. Flemin » Wed, 27 May 1998 04:00:00


Your problem is most likely that your table has multiple unique indexes,
and Access always chooses the one with the lowest-alphabetic-order name
to use as the primary key, which may not be the actual primary key for
the table. If something on the database side causes a column in the key
that Access chose to change after the INSERT operation (like a trigger
on the table), Access will have this problem.

In Access, go into Design mode on the table, bring up the Indexes, and
check to see which one has the "Key" icon next to it. If that's not
really your primary key, you'll need to rename the appropriate indexes
so that Access will choose the right one when you link the table.

It's a pain, I know, but there's not currently any way around it.
Microsoft chose not to ask the database which index is the primary key
when linking the table, instead of picking on themselves. There are
articles in MS' KnowledgeBase about this, but they have no additional
solutions.


> Hi folks,

> we are using Access as the front end to develop our front end to a
> Personal Oracle 8 Dbase running under NT Server 4.0.  We established the
> ODBC connection, and we created linked tables to the Oracle tables and
> we can access them without problems (well, most of the time).

> A problem just showed up, one of our tables shows up with the word

>         #Deleted

> in place of the data that is supposed to be in the table.  The username
> we are logging in as has full access to the Dbase, the table first
> showed up without any problems, then the error showed up.  We removed
> the data from the table, and tried things again, and at the first
> attempt it worked, five minutes later, the problem showed up again (when
> we accessed the tables again).

> Now this is a pain in the neck.  If this is a problem that might show up
> later, it would be a major problem.  Of course we couldn't get any help
> from the Access help file.

> If you have any suggestions or if you have faced this problem, I would
> really appreciate any info.

> Thanks,

> Sincerely,

> Abdullah Bushnaq


 
 
 

1. instead of delete trigger delete data from table using execute and temporary table for deleted

Hello,

SQL Server 2k has got me confused...below the instead of trigger that
causes all my problems...below the trigger the explanation of my
problem

===============================================================================
create trigger [triggerConcurrenten] on [dbo].[concurrenten]
instead of delete
as
begin
        -- ***** Declare local vars




        begin transaction

        -- ***** Initialize



        select deleted.* into #deleted from deleted

        /*
        Delete data from concurrenten table using the below sql
statement
        works perfectly.
        =============
        delete
        from concurrenten
        where exists(
        select * from #deleted d
        where concurrenten.[id] = d.[id])
        */

        /* Delete using the execute function fails! */







        begin
                raiserror('[triggerConcurrenten]: Error occured!', 16, 1)
                rollback transaction
        end

        commit transaction      
end

===============================================================================

In the trigger I've tried a 'direct' sql statement to delete data from
the concurrenten table. No problem at all...but when I use the execute
function the exact same delete statement fails...what am I doing
wrong?

Thanx in advance for the answer!

Best regards,

Peter

2. table owner name

3. Instead of trigger delete fails with NO Error

4. Drop all 'user' tables and import all tables from an Access DB

5. VB6 Data Control + Microsoft Access (French) = Error 525 (Data Access Error)

6. GTTs, workings of.

7. Deleted flag instead of delete?

8. CompactDatabase Method Not Changing Version Property

9. No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

10. INSTEAD OF DELETE and JOIN DELETED

11. Access to Text Data-Type Fields inside Instead-Of-Triggers

12. INSTEAD OF UPDATE triggers (Oracle8)

13. Data Migration from Access to Oracle8