Linked Oracle db in Access 2000

[slapana]

I created a db in Oracle and then linked the tables in Access 2000. I'm
having a problem with the triggers that I created in Oracle. Only one of my
triggers works in Access eventhough they are enabled and valid in Oracle.
The triggers that aren't working are included in some mapping tables that I
created to support one-many relationships amongst various tables. Since my
triggers don't work, the mapping tables, and consequently the forms that use
them aren't working properly.

Is there an easy fix to get the triggers working in Access?

 

[Brian Camire]

What do the triggers do? Populate a column with the next value from a
sequence when each row is inserted?

How are they "not working"? Is it that you can't "see" the value (or you
"see" the incorrect value) in the column that the trigger populates after
you insert a row?

Triggers in Oracle should "work" (that is, execute) regardless of how you
access the data, be it through Access, SQL*Plus, Java, or whatever.

 

[slapana]

Yes, that's what they are supposed to do.

I have tables set up like this: I have a table that is used to collect
information on a particular patient (primary key is an id number generated by
the aforementioned trigger), a table that holds diagnosis information on each
patient (primary key is also a trigger-generated id number), and a table that
maps these two tables together (has the trigger-generated id number (PK) and
includes the PK's from the other two tables as FK's) . The triggers for the
patient and diagnosis primary keys work fine. The mapping table trigger for
it's primary key doesn't work.

Since there is no primary key being generated in the mapping table (at least
I think that's the reason), I can't get a form that includes the patient
table as the main form and the diagnosis table as a subform to work correctly.

I'm new to using Access with an Oracle db, so any help would be greatly
appreciated!

 

[Brian Camire]

Why do you need the third table if the second table already "holds diagnosis
information *on each patient*"?

In any case, in my experience, to get Access to correctly handle linked
Oracle tables that have columns populated by triggers, you must:

1. Create a primary key or unique constraint in Oracle on a column or set
of columns whose value(s) are "known" when a row is inserted (that is, are
not populated by a trigger).

For example, in your case you might need a unique constraint on each table
in addition to the primary keys on the trigger-populated columns. In the
"diagnosis" table, this might be on a DIAGNOSIS_NAME column, if there is
one. In the "mapping" table, this could be on the combination of the two FK
columns. In the "patients" table, your choices might be less clear (for
example, PATIENT_NAME is probably not unique). If you don't have a
"candidate" key, you might in the BeforeInsert event of your form open a
recordset on a linked view or pass-through query that gets the next sequence
number, and then set the value of the bound PATIENT_ID column to the value
returned. You would need to modify your trigger so that it does not
populate the PATIENT_ID column if a value is provided.

2. Create a pseudo-index on the linked table *in Access* (check the help)
on the column(s) from 1.

When you link tables, Access sometimes creates pseudo-indexes automatically
based on the constraints and indexes defined on the source table. Sometimes
these are not the ones you want. To work around this, you have to drop the
pseudo-indexes Access creates (or link to an Oracle-defined view of the base
table, for which Access will not create pseudo-indexes), and then create the
ones you want.


Hope this helps.

 

[slapana]

I guess I don't really need the third table; I was building it that was at
the suggestion of someone else to better handle the one-to-many relationship.
I think that I'm going to eliminate the third table, which is causing me to
do double work.

I'll keep your ideas in mind, though, on the unique constraints if I decide
to keep the third table.

Thanks so much for your help!

 

[Brian Camire]

I expect you will still need the unique constraints on the other two tables.

You typically need a "third" table to model *many*-to-many relationships.
For example, if you have

PATIENTS:
PATIENT_ID, PATIENT_NAME,...
1, John,...
2, Bob,...
3, Mary,...
..
..
..

and

CONDITIONS:
CONDITION_ID, CONDITION_NAME,...
1, Measles,...
2, Mumps,...
3, Rubella,...
4, Chicken Pox,...
..
..
..

You might want to keep track of when patients were diagnosed with certain
conditions (which in this case could happen more than once) using a table
like this:

DIAGNOSES:
DIAGNOSIS_ID, PATIENT_ID, CONDITION_ID, DATE_DIAGNOSED,...
1, 2, 1, 1/1/2004,...
2, 2, 2, 2/1/2004,...
3, 2, 1, 10/1/2004,...
4, 3, 2, 10/14/2004,...
..
..
..

So, John was diagnosed with measles on January 1 and again on October 1.
John was also diagnosed with mumps on February 1, and Mary was diagnosed
with mumps on October 14.

In this example, you might have a unique constraint on PATIENT_ID,
CONDITION_ID, and DATE_DIAGNOSED. This allows many patients to be diagnosed
with the same condition, the same patient to be diagnosed with many
conditions, and the same patient to be diagnosed with the same condition
more than once, but not on the same date.

Sometimes, the combination of foreign keys in these kinds of "junction"
tables is unique by itself.

 

[slapana]

That's exactly what I need!!! Thanks!

I expect you will still need the unique constraints on the other two tables.

You typically need a "third" table to model *many*-to-many relationships.
For example, if you have

PATIENTS:
PATIENT_ID, PATIENT_NAME,...
1, John,...
2, Bob,...
3, Mary,...
..
..
..

and

CONDITIONS:
CONDITION_ID, CONDITION_NAME,...
1, Measles,...
2, Mumps,...
3, Rubella,...
4, Chicken Pox,...
..
..
..

You might want to keep track of when patients were diagnosed with certain
conditions (which in this case could happen more than once) using a table
like this:

DIAGNOSES:
DIAGNOSIS_ID, PATIENT_ID, CONDITION_ID, DATE_DIAGNOSED,...
1, 2, 1, 1/1/2004,...
2, 2, 2, 2/1/2004,...
3, 2, 1, 10/1/2004,...
4, 3, 2, 10/14/2004,...
..
..
..

So, John was diagnosed with measles on January 1 and again on October 1.
John was also diagnosed with mumps on February 1, and Mary was diagnosed
with mumps on October 14.

In this example, you might have a unique constraint on PATIENT_ID,
CONDITION_ID, and DATE_DIAGNOSED. This allows many patients to be diagnosed
with the same condition, the same patient to be diagnosed with many
conditions, and the same patient to be diagnosed with the same condition
more than once, but not on the same date.

Sometimes, the combination of foreign keys in these kinds of "junction"
tables is unique by itself.