Show Table Relationships

In Access, is it important to show table relationships in the Relationship window?  If so, why.
Thanks,
Saleve
SaleveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
twintaiCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
SaleveAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:


"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
twintaiCommented:
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
SaleveAuthor Commented:
Sorry twintai.  I gave out the points before I saw your last comment...  Thanks for the info.  It is helpful.
Regards,
Saleve
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
twintaiCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.