identify join sides of a table

  • Thread starter Thread starter Ben8765
  • Start date Start date
B

Ben8765

Hi,

I am looping through all the tables in a db and appending data to each table.

The problem is that I can't append data to a table on the "many" side before
i append the necessary data to the "one side" table.

Is there a way to identify the join types (or "sides) that a table is
involved in?

That way i could loop through and append the tables that are on the "one"
side first.
Then do the tables that are on the "many side".

I suppose this could get tricky because the table could be on the "many"
side of one relationship, and on the "one" side of another relationship.
That would mean I would have to make some sort of hierarchy/order system (I
think?).

Anyway, is there a way to determine if a table is involved in a relationship
that would result in key violations?

Thanks,

-Ben
 
Ben8765 said:
I am looping through all the tables in a db and appending data to each table.

The problem is that I can't append data to a table on the "many" side before
i append the necessary data to the "one side" table.

Is there a way to identify the join types (or "sides) that a table is
involved in?

That way i could loop through and append the tables that are on the "one"
side first.
Then do the tables that are on the "many side".

I suppose this could get tricky because the table could be on the "many"
side of one relationship, and on the "one" side of another relationship.
That would mean I would have to make some sort of hierarchy/order system (I
think?).

Anyway, is there a way to determine if a table is involved in a relationship
that would result in key violations?
Click to expand...


Well, Access knows how to do that when it checks referential
integrity, but when I tried to create a function that could
sort a database's tables in their dependency order, I found
it to be extrordinarily complex. I finally gave up when I
got to the issue of multiple field indexes and tables that
were dependent on each other in a cirular fashion.

If are sharp enough to wade through all the complexities,
you can loop through a specific table's Indexes collection
looking for indexex that have their Foreign Key property set
to see if they are on a many side of a 1-M relationship.
Tracing that to the 1 side table and repeat until you get to
a table with no foreign keys. Personally, I found it to be
a headache I could not bear with.

Nowhere near as nasty is to create a table with a text field
for table names and a number field for the order that you
can update them. Then use a query that sorts the list of
tables by their update order field and use that to control
your update loop. The biggest problem with this kind of
workaround is to remember to keep the helper table in sync
with your real tables when you make a change to their
relationships.
 
Back
Top