Re: Please help

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 09/11/04 

Date: Sat, 11 Sep 2004 15:23:24 -0400

Not a generic one, no. Too many ways in which the programming will need to
do this, and you will need to know *all* the tables and how they relate to
each other so that the programming will do everything that's necessary.

Essentially, what your programming would need to do is one or more of the
following:

(1) when a user wants to delete a parent record, test if there are child
records related to that parent record (there can be *many* child tables),
and to cancel any such deletions. This could be done using DCount function
with a criterion expression based on the parent record's linking fields.

(2) when a user wants to delete a parent record, test if there are child
records related to that parent record (there can be *many* child tables),
and to delete all such child records too. This could be done using a delete
query for each table, with a criterion expression based on the parent
record's linking fields.

(3) when a user wants to change the primary key field's value for a parent
record, test if there are child records related to that parent record (there
can be *many* child tables), and to change the foreign key field's values
for all such child records too. This could be done using an update query for
each table, with a criterion expression based on the parent record's linking
fields.

(4) when a user wants to add a child record using a value for the foreign
key field for which there is no parent record with a matching primary key
field value, cancel the addition. This could be done using DCount function
with a criterion expression based on the value of the foreign key and the
linking field in the parent record. 

-- 
        Ken Snell
<MS ACCESS MVP>
"Al" <anonymous@discussions.microsoft.com> wrote in message
news:075d01c4982d$f3057d30$a301280a@phx.gbl...
> Could you please give me a programming example for the
> front end to do what referential integrity would have done
> for me?
> thanks
> Al
>
> >-----Original Message-----
> >No, you cannot relate two tables that are in different
> backends. You'll need
> >to do that in your queries (via the join lines) and
> you'll need to do in
> >your front end's programming what referential integrity
> would have done for
> >you.
> >
> >-- 
> >
> >        Ken Snell
> ><MS ACCESS MVP>
> >
> >"Al" <anonymous@discussions.microsoft.com> wrote in
> message
> >news:065d01c49808$66904df0$a301280a@phx.gbl...
> >> What I am saying is that I am going to take the whole
> >> table out of the main back end and put it in its own
> >> database then link the table agian to the front end,
> i.e.
> >> the front end will be linked to 2 back ends, one with
> only
> >> this table and the other will have the rest of the
> tables.
> >> To illustrate:
> >> My main table, the one side in the relationship is
> called
> >> tblPatientDemographic. My many side table is called
> >> tblProgressNotes. tblProgressNotes is very big with memo
> >> field in it. I took tblProgressNotes out to its own db
> >> then I linked tblPatientDemogrphic from the original
> back
> >> end to the db with tblProgressNotes. Since
> >> tblPatientDemographic is linked, it does not allow me to
> >> recreate the relationship with tblProgressNotes and
> >> enforce the referential integrity. It only allows me to
> >> just link both tables. Is there a way to maintain the
> >> referential integrity?
> >> thanks
> >> Al
> >> >-----Original Message-----
> >> >Are you saying you're going to split the table across
> two
> >> backends? If yes,
> >> >then you won't be able to maintain referential
> integrity
> >> by ACCESS. You'll
> >> >need to do that in your programming when records are
> >> edited/deleted/added.
> >> >
> >> >-- 
> >> >
> >> >        Ken Snell
> >> ><MS ACCESS MVP>
> >> >
> >> >"Al" <anonymous@discussions.microsoft.com> wrote in
> >> message
> >> >news:026801c49773$0e89ed90$a401280a@phx.gbl...
> >> >> I have a back end with a table that is growing very
> big
> >> >> and I am having to move it to a different back end
> and
> >> >> link it to the same front end. How can I keep the
> >> >> referential integrity intact.thanks
> >> >> Al
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • Re: Please help
    ... >>records related to that parent record (there can be ... > *many* child tables), ... >>>>your front end's programming what referential integrity ... >>>>> tblProgressNotes. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is there a religion gene?
    ... by those we are programmed to respect, parents, elders, etc. ... the argument about child brains and religion. ... be eaten by crocodiles" is good advice but "If you don't sacrifice a goat at ... Some of us throw off that programming more easily than others, ...
    (uk.philosophy.atheism)
  • Re: Whos your mom, ChildForm?
    ... I am using a separate form. ... the parent and child *tables* properly related in the Relationships window? ... if one parent record may have many related child records, ... primary key; plus, the primary key values of the parent records must be ...
    (microsoft.public.access.forms)
  • Re: Require a record in a subform
    ... In a relational database the parent record must be saved before any child ... > the user they must add a child record before closing the form. ...
    (microsoft.public.access.forms)
  • Re: Not in List Event Error
    ... I’ll leave it to you to do what ever you want to do with your database. ... are related records in child tables. ... In all of my years of programming, only one program required tables based on ... RI is absolutely incorrect on the basis of a one-to-one relationship, ...
    (microsoft.public.access.formscoding)
Quantcast