Solved

Show Table Relationships

Posted on 2007-04-06
11
606 Views
Last Modified: 2013-11-29
In Access, is it important to show table relationships in the Relationship window?  If so, why.
Thanks,
Saleve
0
Comment
Question by:Saleve
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 25 total points
ID: 18863328
<<In Access, is it important to show table relationships in the Relationship window?  If so, why.>>

  The relationship window is more then just a picture of the relationships between tables; you are actually *creating* a relationship between tables.  And generally yes, you want to do this.  By establishing a relationship, your enforcing that certain rules be followed at the engine level (outside of your code).

  For example, that a parent record must exist before a child record can be created.  You can also indicate that if a parent is deleted, then all it's children are as well (cascading delete).  There is cascading updates as well.  Not everyone uses those last two features, but one usually defines relationships at the very least.

JimD
0
 
LVL 57
ID: 18863341
<<The relationship window is more then just a picture of the relationships between tables; you are actually *creating* a relationship between tables. >>

  Too make that a bit clearer, it's the *line* between the tables that is the relationship.  If you double click on it, you will get a property page.

JimD
0
 
LVL 6

Expert Comment

by:twintai
ID: 18864941
All the previous posts are correct. I am curious, are you trying to do something specific? Or is there a problem with the window?
0
 
LVL 75
ID: 18865419
The important thing is ... to **have** relationships ... and when you do, they will ... show in the Relationships window ... since, short of creating them via VBA code ... you will create them in the Rels window ... and thus they will show up.  

The subject of creating relationships and Referential Integrity, Cascade Updates/Deletes is a non-trivial, but *very* important subject ... and is the very first step that must be done when creating a (relational) Access database.  

The 'relational structure' will be your 'road map' that you will refer to constantly throughout the development process and beyond and ... will help insure the integrity of the database.  Relational Design is one of my favorite subjects, and sadly ... is not that well understood by many people ... and generally somewhat ignored or covered very little in Access text books.

So, if necessary, we can get down to some specific questions on the subject? Also, there are many good links right here on EE about the subject.

mx
0
 

Author Comment

by:Saleve
ID: 18865696
twintai ,
The database is done, and while I believe that the relational structure is sound, I did not (yet) display it in the Relationships window.   (I plan to do it, however.)

The reason for this is two-fold:
1)The first thing I did after making the tables, was set up the relationships in the Relationships window.  But everytime I needed to delete a table (some structural modifications were needed in the early stages) I had tofirst delete the relationships.  This got tiring, so I finally just deleted everything from the Relationships window.
2) When I first set up the relationships, I enforced referential integrity - cascade delete related records.  But then I was not able to add a record to one table before the related record in another table was populated.  This seemed like a chicken-and-egg problem.  I am MOST curious about this!

MX,
Specifically, while I have no problems now to set up the relationships in the Relationships window, 1) why is it so important to do once the database is done (does it change the performance?) and 2) how do I enforce referential integrity?
Thank you,
Saleve
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 57
ID: 18865740
<<1)The first thing I did after making the tables, was set up the relationships in the Relationships window.  But everytime I needed to delete a table (some structural modifications were needed in the early stages) I had tofirst delete the relationships.  This got tiring, so I finally just deleted everything from the Relationships window.>>

  That's common.

<<2) When I first set up the relationships, I enforced referential integrity - cascade delete related records.  But then I was not able to add a record to one table before the related record in another table was populated.  This seemed like a chicken-and-egg problem.  I am MOST curious about this!>>

  Well that's what referential integrity means; you can't have a child without a parent already existing.

JimD
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 100 total points
ID: 18865884


"why is it so important to do once the database is done (does it change the performance?) "
Well ... it's important to do it period, 'done' or otherwise.  It is **always the very first thing that I do ... including setting Ref Integrity. w/o Ref Integrity applied, you do **not** really a true relationship.  You may ...have line connected between two fields in a table, but w/o RI set (applied) ... you do **not** actually have a relationship.

Performance. DEFINITELY.  The Jet engine is able to make major optimizations on queries that you create on tables what have established relationships between them, wherein w/o relationships, Jet cannot do that. So Yes ... major effect on performance ... *all other things being equal* ... as the saying goes.

"how do I enforce referential integrity?

JimD has identified the mostly likely issue regarding that.  Now, the 'theory' on exactly 'how/where' etc you create RI ... again, a bit beyond the scope of this particular question ... ie, not a one or two line answer.

mx

0
 
LVL 6

Expert Comment

by:twintai
ID: 18867517
MX is right. It's just the way Access Db is set up. The converse option you have it to not have any relationship connections made, but at this point, it kind of defeats the point of MS Access. Unlike most true RDBMS, Access gives you tools for Front End and Back End development. It's not working with MySQL or MsSQL, where you can only work on the back end. Then you would have to put more work in to develop the Front End.

If you decide not to make the connections is the Relationship Window, you can technically get a way with it, but you will literally had to hard code all the relationships on the form side. You know, when a data is entered in a parent form, remember the PK so that it can be used as a FK in the child form, and so and so on.

 But, let me try to explain how things work with the relationships. You can not delete a record from a Parent Table unless all records are deleted from the Child Table. Also, you can add a record to the Child Table unless there is a relating record in the Parent Table. These kind of rules are just what makes Databases what they are - alot more efficient than using something like Excel for data collection and management.

That's alot. Hope all makes sense.
0
 

Author Comment

by:Saleve
ID: 18867569
Sorry twintai.  I gave out the points before I saw your last comment...  Thanks for the info.  It is helpful.
Regards,
Saleve
0
 
LVL 75
ID: 18867594
Thanks for the confirmatiion TT.

"Unlike most true RDBMS, Access"   ????

"You can not delete a record from a Parent Table unless all records are deleted from the Child Table."

??

mx



0
 
LVL 6

Expert Comment

by:twintai
ID: 18867791
Saleve...For some reason, I actually I didn't notice that you had aleady assigned point to MX and JDettman. Besides, it's no big deal. I'm here to learn as well as teach. As long as my comments are helpfu.

MX and JDettman are on point and deserving of the points....On to the next learning experience.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now