[AccessD] 2 Questions

Asger Blond ab-mi at post3.tele.dk
Tue Mar 9 17:07:00 CST 2010


Hey Tony,
Not sure I'm getting your point here.
Anyhow I think it's important to distinguish two topics which the Relationships-Tool in Access tends to blur:
a) Joining fields.
b) Establishing foreign key constraints ("referential integrity") between fields.
If you don't check the option "Enforce referential integrity" when you use the Relationship-Tool then all you get is a convenient way to have an automatically join set up between the fields when you use the query builder. You can of course set up these joins manually yourself each time in your query builder, but I find this feature very nice and a good reason to use the Relationship-Tool. If you are writing the queries yourself in SQL (not using the query builder) then this feature is of no importance for you.
But establishing foreign key constrains is a quite different beast. It has per se nothing whatsoever to do with joining fields in *queries*: it is there for enforcing consistency between values in different fields when you are doing *inserts* and *updates*. Sure you can establish foreign key constraints by code, but I don't know why on earth you would do it that way since you can much more easily do by checking the "Enforce referential integrity" option using the Relationship-Tool.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Tony Septav
Sendt: 9. marts 2010 23:05
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] 2 Questions

Hey Asger
I am not trying to start a debate. But if I do my joins in a query (with 
many many tables joined), if  I visually check my results  and find an 
error (or add code to test for errors), I can strip everything down  and 
rebuild it back up step by step.  This way I can verify my results and 
ensure referential integrity. If  1 does not equal 1 then I am in trouble.

Asger Blond wrote:

>2. I use Tools | Relationship to create foreign key constraints ("enforce referential integrity"). Without foreign key constraints the data will soon loose consistency.
>
>Asger
>
>-----Oprindelig meddelelse-----
>Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Max Wanadoo
>Sendt: 9. marts 2010 21:45
>Til: 'Access Developers discussion and problem solving'
>Emne: Re: [AccessD] 2 Questions
>
>Tony:
>
>1. At the top of  the module put
>Option explict
>
>Then compile it, all the unspecified variable will be picked up.
>
>
>2. Me neither.  I seldom use relationship and much prefer to handle them in
>code.
>
>Max
> 
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tony Septav
>Sent: Tuesday, March 09, 2010 7:25 PM
>To: Access Developers discussion and problem solving
>Subject: [AccessD] 2 Questions
>
>Hey All
>Just pondering
>1. Access2003 does not seem to pick up erroneous variables.
>If on a form I assign for a control
>Dim JTnum as double
>then in code
>..................
>I make a spelling error
>Jnum = rst.Pnum
>...................
>rst!dnum=jnum (which gives me 0).
>When I run a compile I do not get an error message.
>In previous versions of Access I am pretty sure the above would generate an
>error message. Have I inadvertently turned something off????
>
>2. I do not use the Tools Relationship.
>I establish them in my queries and in my forms/subforms link or in code.  I
>also (because I have never liked Access's error messages) warn users with my
>own error message if a problem might occur.  Is this a feature designed for
>non-developers just like the table lookup (which I don't use either) or am I
>missing something (life has been good without it, just curious)????
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>  
>

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list