Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

How to enforce cascade update/delete?

Discussion in 'Information Technology' started by Chris Burnette, Nov 1, 2005.

  1. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    I have an MSDE database with an Access front-end. I have 4 tables in this
    DB; one parent and three child tables. The PK of the parent table is used as
    a Foreign Key in each of the child tables, and enforce referential integrity
    is checked as well as cascade update and delete.

    The problem is that when I go to create a new record in the Access front-end
    (which uses an Autonumber data type for the parent table PK), the data does
    not appear in any of the other tables. This is a problem because I need to
    have the same # of records in all 4 tables, and when I create a new record I
    need one field (in this case the parent table PK) to be the same for all the
    tables so that I can do joins.

    Does anyone have any idea why the updates are not cascading? Any help would
    be appreciated.

    Thanks,

    Chris
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Unfortunately, you've misinterpretted what cascade update is.

    Cascade update means that any time you change the primary key of a record in
    the primary table, Microsoft Access automatically updates the primary key to
    the new value in all related records. For example, if you change a
    customer's ID in the Customers table, the CustomerID field in the Orders
    table is automatically updated for every one of that customer's orders so
    that the relationship isn't broken. Microsoft Access cascades updates
    without displaying any message.

    It's generally not considered to be a good idea to create dummy records as
    place holders. Once you have values to store in the child tables, then
    create the records, not before.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    news:57584B96-B638-4AE4-8378-183D29F363ED@microsoft.com...
    >I have an MSDE database with an Access front-end. I have 4 tables in this
    > DB; one parent and three child tables. The PK of the parent table is used
    > as
    > a Foreign Key in each of the child tables, and enforce referential
    > integrity
    > is checked as well as cascade update and delete.
    >
    > The problem is that when I go to create a new record in the Access
    > front-end
    > (which uses an Autonumber data type for the parent table PK), the data
    > does
    > not appear in any of the other tables. This is a problem because I need
    > to
    > have the same # of records in all 4 tables, and when I create a new record
    > I
    > need one field (in this case the parent table PK) to be the same for all
    > the
    > tables so that I can do joins.
    >
    > Does anyone have any idea why the updates are not cascading? Any help
    > would
    > be appreciated.
    >
    > Thanks,
    >
    > Chris
     
  4. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Cascade Update means that if you change the value of the PK, the database
    engine will automatically change the corresponding FK to the new value also
    so that you don't lose the link between the Parent Record and the Child
    Records.

    Since you use Identity Field (equivalent to AutoNumber Field in JET), you
    cannot change the value of the PK anyway.

    Cascade update does not mean creating the Child Records automatically when
    the Parent Record is created. There is no way that the database engine
    knows how many Child Records you need (One-to-*Many* relationship) and other
    details for the Child Records.

    When you use Access Form and Subform with appropriate LinkMasterFields /
    LinkChildFields, Access will enter the appropriate value for the FK for the
    new Child Record in the Subform from the PK value of the Parent Record
    currently on the main Form.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    news:57584B96-B638-4AE4-8378-183D29F363ED@microsoft.com...
    >I have an MSDE database with an Access front-end. I have 4 tables in this
    > DB; one parent and three child tables. The PK of the parent table is used
    > as
    > a Foreign Key in each of the child tables, and enforce referential
    > integrity
    > is checked as well as cascade update and delete.
    >
    > The problem is that when I go to create a new record in the Access
    > front-end
    > (which uses an Autonumber data type for the parent table PK), the data
    > does
    > not appear in any of the other tables. This is a problem because I need
    > to
    > have the same # of records in all 4 tables, and when I create a new record
    > I
    > need one field (in this case the parent table PK) to be the same for all
    > the
    > tables so that I can do joins.
    >
    > Does anyone have any idea why the updates are not cascading? Any help
    > would
    > be appreciated.
    >
    > Thanks,
    >
    > Chris
     
  5. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    Ah, I see I misunderstood. Thanks for the info anyway, it helps.

    -Chris

    "Van T. Dinh" wrote:

    > Cascade Update means that if you change the value of the PK, the database
    > engine will automatically change the corresponding FK to the new value also
    > so that you don't lose the link between the Parent Record and the Child
    > Records.
    >
    > Since you use Identity Field (equivalent to AutoNumber Field in JET), you
    > cannot change the value of the PK anyway.
    >
    > Cascade update does not mean creating the Child Records automatically when
    > the Parent Record is created. There is no way that the database engine
    > knows how many Child Records you need (One-to-*Many* relationship) and other
    > details for the Child Records.
    >
    > When you use Access Form and Subform with appropriate LinkMasterFields /
    > LinkChildFields, Access will enter the appropriate value for the FK for the
    > new Child Record in the Subform from the PK value of the Parent Record
    > currently on the main Form.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in message
    > news:57584B96-B638-4AE4-8378-183D29F363ED@microsoft.com...
    > >I have an MSDE database with an Access front-end. I have 4 tables in this
    > > DB; one parent and three child tables. The PK of the parent table is used
    > > as
    > > a Foreign Key in each of the child tables, and enforce referential
    > > integrity
    > > is checked as well as cascade update and delete.
    > >
    > > The problem is that when I go to create a new record in the Access
    > > front-end
    > > (which uses an Autonumber data type for the parent table PK), the data
    > > does
    > > not appear in any of the other tables. This is a problem because I need
    > > to
    > > have the same # of records in all 4 tables, and when I create a new record
    > > I
    > > need one field (in this case the parent table PK) to be the same for all
    > > the
    > > tables so that I can do joins.
    > >
    > > Does anyone have any idea why the updates are not cascading? Any help
    > > would
    > > be appreciated.
    > >
    > > Thanks,
    > >
    > > Chris

    >
    >
    >
     

Share This Page