If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linked Oracle db in Access 2000
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? |
#2
|
|||
|
|||
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" wrote in message ... 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? |
#3
|
|||
|
|||
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" wrote: 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" wrote in message ... 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? |
#4
|
|||
|
|||
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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#5
|
|||
|
|||
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
#7
|
|||
|
|||
That's exactly what I need!!! Thanks!
"Brian Camire" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access XP Compared to Access 2003 | Mardene Leahu | New Users | 1 | October 1st, 2004 05:11 AM |
Access 2000 DB in Access 2002 | Tony_VBACoder | General Discussion | 2 | July 28th, 2004 01:23 AM |
SQL Server 2000 Stored Procedures to MS Access 2000 Queries | CS | General Discussion | 4 | July 15th, 2004 03:27 AM |
access 97 and access 2000 problem/question... | warpman | General Discussion | 5 | June 20th, 2004 03:16 AM |
Cannot use OpenArgs in Access 2000 | Peter Afonin | Setting Up & Running Reports | 4 | May 18th, 2004 07:19 PM |