Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using AutoLookup (2000)

    I can create AutoLookup queries, but I came across the following statement in a book which I don't understand: "If you want to update the value of the join field from the one side, you must have referential integrity enforced with cascading updates ticked". What does this mean? Andy.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using AutoLookup (2000)

    Say, you have a Products table with fields ProductID (text, PK) and ProductName (text) (plus other fields if needed.)

    <table border=1><td>ProductID</td><td>ProductName</td><td>C01</td><td>Cheddar</td><td>C02</td><td>Wensleydale</td><td>F01</td><td>Cod</td><td>F02</td><td>Salmon</td></table>
    And you have an Orders table, with among others, a ProductID field:

    <table border=1><td>OrderID</td><td>OrderDate</td><td>ProductID</td><td>Quantity</td><td align=right>10112</td><td align=right>03/03/04</td><td>C02</td><td align=right>24</td></table>
    There is a relationship between the tables on ProductID. Now, for some reason, you decide to change the ProductID for Wensleydale cheese to C37.
    <UL><LI>Without referential integrity, the above record in the Orders table would be orphaned: its OrderID would not refer to an existing product any more.
    <LI>With referential integrity, but without cascading updates, you would get an error message that the record can't be modified because there are related records in the Orders table.
    <LI>With referential integrity and cascading updates, the ProductID field in the Orders table would be changed from C02 to C37 automatically, so that the relationship between the records remains the same.[/list]That's why you need referential integrity with cascading updates.
    Note: if the primary key on the "one" side is an AutoNumber field, it can never be changed once it has been created, so cascading updates are irrelevant. Referential integrity is still important.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using AutoLookup (2000)

    That's quite clear, thank you. How does it relate to an AutoLookup query though? If I build an AutoLookup query, when I key the ProductID Access will 'fill-in' the ProductName for me. If I try and change the ProductID code to something that doesn't exist an error message will arise to say it cannot be found. I think the original statement that I quoted is incorrect in terms of an AutoLookup query?!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using AutoLookup (2000)

    You don't really need referential integrity for an AutoLookup query: according to the online help for Access 2002, the first prerequisite for an AutoLookup query is
    <hr>The query must be based on more than one table and the tables must have a one-to-many relationship. (Referential integrity doesn't have to be enforced.)<hr>
    Still, it is advisable to use referential integrity in most situations, to avoid records becoming orphaned.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using AutoLookup (2000)

    Just on that last point, under what circumstances would referential integrity not be enforced? I can only think of two circumstances: where a 'simple' lookup is supplied to assist in data entry (for a Title field supplying Mr, Mrs, Miss, Ms, Dr) or where information is being imported from, for example, Excel and it is in a 'raw' state. Are there other circumstances that you can think of?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using AutoLookup (2000)

    I always enforce referential integrity wherever possible...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •