Solved

Show Table Relationships

Posted on 2007-04-06
11
609 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 
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 - Microsoft MVP, Access and Data Platform) 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

726 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