Is there a benefit of setting up relationships in A2003?

[paulwilliamsonremove]

Hi,

I have been manually setting up relationships in Access 2003. I
received an error message when leaving a form that told me the record
could not be saved because I had to have a related record in another (
child) table.

After researching the problem, I decided to open up the table
relationship manager and selected the "Show All" option.

I believe Access automatically sets up relationships when needed
(doesn't "Show All' display relationships not set up by the
developer?). I went through each relationship and found that Access
incorrectly set up a relationship between two fields from two tables
that have different field names (one of them was not even a primary
key field!).

So why should I bother manually setting up relationships?

Thanks
-pw

use paulwilliamson at spamcop dot net for e-mail

[pietlinden]

Umm... how about control? And enforcing correct referential integrity.
So you can't have childless parents. (You have your terminology
backwards.)

Kinda important that you can't have a bank transaction without having
an account... of course, you *can* do the cascading update/delete stuff
in code, but it's a lot more work... and you can do the fun stuff that
Oracle lets you do, like doing SET NULL...

how you handle enforcement of referential integrity is up to you - you
can use code or let Access handle it... depends what you need...

[Allen Browne]

The only way to construct a reliable database is to set up the relations
yourself.

There are typically lots of hidden indexes in an Access database. If you
suspect you also have hidden relations, you can list them programmatically
like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function

Stay away from the Lookup Wizard in table design. It's likely to do all
sorts of stuff behind your back. More info in this article:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa******************@removespamcop.net> wrote in message
news:08********************************@4ax.com...

I have been manually setting up relationships in Access 2003. I
received an error message when leaving a form that told me the record
could not be saved because I had to have a related record in another (
child) table.

After researching the problem, I decided to open up the table
relationship manager and selected the "Show All" option.

I believe Access automatically sets up relationships when needed
(doesn't "Show All' display relationships not set up by the
developer?). I went through each relationship and found that Access
incorrectly set up a relationship between two fields from two tables
that have different field names (one of them was not even a primary
key field!).

So why should I bother manually setting up relationships?

Thanks
-pw

use paulwilliamson at spamcop dot net for e-mail

[paulwilliamsonremove]

On Thu, 7 Jul 2005 13:51:59 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

The only way to construct a reliable database is to set up the relations
yourself.

There are typically lots of hidden indexes in an Access database. If you
suspect you also have hidden relations, you can list them programmatically
like this:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable, rel.Attributes
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function

Stay away from the Lookup Wizard in table design. It's likely to do all
sorts of stuff behind your back. More info in this article:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

Thanks Allen.
-pw

use paulwilliamson at spamcop dot net for e-mail