Saving Contol Data in Multiple Tables (1 Viewer)

[mstorer]

I building a program which saves insurance policy data in IBM DB2 tables. I have different tables for specific lines on the policy (i.e. property, automobile, etc.) Admittedly, my knowledge of DB2 is not as advanced as this project demands but am locked into this scenario. I would like to enforce cascading updates on the different tables and do not know how to perform this operation in DB2. I believe that Access 2000 does not allow for the enforcement of referential integrity of linked ODBC tables. To get around this, I was hoping to save the information entered into a form's control to multiple tables. Can Access store information entered in one control to multiple tables?

I suppose the second question is if this is a wise idea in the scope of table normalization. My gut says "No". If I go this route, there will be tables with only the one key field entered. Thanks for any insight.

 

[Pat Hartman]

Referential integrity is ALWAYS defined and enforced by the database engine so if your tables are in a DB2 database, DB2 defines and enforces RI. If the tables are in an Oracle db, Oracle does RI. If the tables are in an Access db, Jet enforces RI. The point of this being that it does not matter what application is modifying data, ALL changes to data go through the database engine so that is the logical place to enforce RI. How would a COBOL program running on the mainframe, be aware of RI that you defined in your Access db that might be sitting on your hard drive on your PC which might be turned off? It couldn't. That's why you can't define RI for ODBC tables in an Access db. It's not a limitation of Access. It simply doesn't make sense.

By your question, I can tell that you do not understand what cascade update does. Cascade update propagates key value changes to any foreign key references. For example, if you had a country table and used codes as the primary key, you might have used "CEY" for Ceylon. When the country name was changed to Sri Lanka, you would have wanted to change the key to something more appropriate such as "SRI". The country name field would have been used as a foreign key in your company address table. So changing the value "CEY" to "SRI" in the country table would "cascade" that change to ANY records in the company address table that contained the value "CEY" in the country field.

Changes to non-key fields are NEVER cascaded. No RDBMS would ever support this process since duplicating non-key data violates basic principles of database design.

 

[mstorer]

Thanks for the feedback Pat as you have always been a great source of knowledge and support. You hit on an excellent point that I did not make clear in my initial post. The cascading updates would, in fact, impact my foreign key references. Since my knowledge of IBM's DB2 is limited, I am trying to use Access to programatically perform the same task as using DB2's RI. I've been through the help files of DB2 but they are vague or simiply over my head.

To make the scenario clear, the database I am developing tracks notes and other information our underwriters make for each line of business on an account. Initially, an account is given a quote number. If the account is actually written, that quote number will change to a policy number. It is this change in quote to policy number that I need cascaded to the my tables. I hope this makes sense.

Clearly the best way to solve this issue is to have DB2 enforce referential integrity. (Time to break out the telepone book size manuals). However, for future reference and basic curiosity, can a single control on a form "populate" fields in more than one table? I looked in the help files and found nothing so I am assuming the answer is "No".

Thanks again for the feedback.