Self-linking table in ADP

  • Thread starter Thread starter Charax
  • Start date Start date
C

Charax

In an Access MDB, I can add a second instance of a table to the
Relationships Diagram as if it were another table. Then I can use a linking
table to provide a many-to-many relationship from the table to itself. By
enforcing referential integrity, the duplicates can be tracked. Example:

tblPhotos tblDuplicates tblPhotos_1
PhotoID ----------------- PhotoID /----------- PhotoID
Filename PhotoID_1 -------/ Filename

pk=PhotoID /PhotoID pk=PhotoID
inherited
pk=<
\PhotoID_1

How can this be done in ADP?

Thanks,

Chris Hopkins
 
In exactly the same way as with MDB.

However, I seriously doubt the necessity of using a self-referencing
relationship to track duplicates. It would be easier to put the photos in
their own table. Self-referencing relationship should be used only for
hierarchical datas.
 
Silvain,

Could you be more specific on how to do this because I do not understand how
it is "exactly the same as with an MDB"? I think there is no Relationships
Diagram in ADP, but instead the Database Diagram objects -- where if I try
to add a second instance of the table, I get this warning:
"One or more selected tables are already on the database diagram.
"You can only have one copy of any table on a database diagram...."

The photos are already in their own table, and I see this as a many-to-many
relationship on the same table, not self-referencing. My question is how to
track the duplicates with a linking table that has PhotoID and PhotoID_1
fields from the tblPhotos with referential integrity enforced -- or some
other method that works in ADP.

Thanks for your help, but please give a little more detail in your answer
because I am new to ADP after many years of using MDBs.

Regards,

Chris Hopkins
 
I'm sorry but you're right: I misread you question and took more attention
to the self-referencing question than the second instance of the table
question. (You can indeed add a copy of the same table with ADP but only
when designing queries (views and procedures), not directly in the database
diagrams).

In ADP's database diagram, you must make the relationship directly from the
table to itself by dragging the foreign key to the same table (which you
cannot do in an Access MDB file). When creating Views or Stored Procedures
in design mode, you must process the same way as with Access MDB file
because you must give an alias name to the second instance in the Select
statement.
 
Many thanks for your kind help, Sylvain. Your advice should get me on the
right track.

Best regards,

Chris Hopkins
 
Back
Top