Rebuilding Relationships

[FBxiii]

No, I am not trying to post this to an agony aunt site

I have created a front/backend system. The front detects changes in the
backend and then re-links all the tables. Unfortunately, this removes the
relationships between the tables. There is 1 'central' table, used for
tracking the data items passing through the DB. Several other tables are
linked to the tracking table via a unique identifier (Primary key).

How/if, using code, do I re-create the relationships? Ive got the
re-linking bit down to a tee, but all the indexes, etc seem to mess up......

Cheers for any advice,
Steve.

 

[Douglas J. Steele]

The relationships must be in the back-end: relationships in the front-end
don't do anything.

That having been said, relinking the tables shouldn't do anything to the
relationships.

 

[FBxiii]

Oh, I see.

So, if I have related the tables in the backend, and create a new
relationship in the frontend (mirroring the relationship at the back), it
actually deletes the relationship from the frontend, but the relation still
exists?

Ahha! I think I understand. Does a relationship created in the frontend
actually do anything?

Cheers,
Steve.

 

[Douglas J. Steele]

Relationships in the front-end don't do anything.

If you've set relationships up to force referential integrity, they won't do
any good unless they're actually in the database that contains the data.
Otherwise, someone could attach to that database from a different front-end
and you wouldn't have RI.

Alternatively, you could have TableA linked to Database1 and TableB linked
to Database2 in your front-end. Setting up relationships is pointless, as
you can't enforce RI across databases (again, someone could attach to
Database1 only, and not even know about Database2)

 

[Marshall Barton]

Specifying relationships in the front end does do something.
It confuses you by misleading you into the mistaken belief
that you actually did something, when all you actually did
was draw some lines between some boxes. Furthermore, if
those lines are different than the ones in the back end,
your queries will also be confused into thinking they have
to perform Joins differently than the way they were
specified in the back end.

 

[John Vinson]

Does a relationship created in the frontend
actually do anything?

VERY little. All it does is make that relationship type the default
when you create a query involving the two tables.

BTW loved the "agony auntie" comment - I've got a VBA routine named
KillAllRelations, and I've been occasionally tempted to send a copy to
my brother-in-law...!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Tim Ferguson]

Does a relationship created in the frontend
actually do anything?

As you have read -- no. Bear in mind, too, that unseen relationships in the
back end still are in effect. Imagine a back end containing tables like

Patients

Doctors

Treatments


with enforced relationships between Patients and Treatments, and between
Doctors and Treatments. Imagine, too, that you want to create a mailing
list front-end that is only linked to the Patients table. Then you note
that Jim Smith deceased some time ago, and want to delete him from the
Patients table. You can't -- because there are related records in the
Treatments table (which you can't see, of course).

Just a thought to keep in mind... Relationships Are Part Of Data!

All the best


Tim F