Please help

[Al]

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

 

[Ken Snell [MVP]]

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.

 

[Al]

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

 

[Ken Snell [MVP]]

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.

 

[Al]

Could you please give me a programming example for the
front end to do what referential integrity would have done
for me?
thanks
Al

 

[Ken Snell [MVP]]

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]

Thank you Ken this is what I was looking for. I have only
these two tables that I am concerned about and the way the
data is entered in the many table is through a form that
has a main and a sub which are linked chiled and master
fields on the "PatientID" which is the key field. I wanted
to know in the case of update(cascade update) should I
keep the event in the before update for the field?
thanks

 

[Ken Snell [MVP]]

No, I think it should be on the AfterUpdate event of the form. That way,
you'll know that the edit has been committed to the record and thus the
value was accepted.