Results 1 to 3 of 3
  1. #1

    Lookups & Referential Integrity

    I sent some private comments to Charlotte and her reply would benefit all users/developers, so here is the exchange as a new thread.


    You indicated a performance hit when using a table level lookup. When does Access initiate a table lookup? In a client server environment it is not too
    smart or secure to not use table level rules when using systems like Oracle, Informix, Ingress, etc, plus you get better performance. I would think that this is more true now that MS has shifted to using a version of SQL server as the engine. I look at the table level lookup as native code. So why is it more preferable to build a form level lookup? I realize there are many instances where a form level lookup with data validation is more preferable than a table

    Access isn't client/server unless you're using an Access 2000 ADP with a SQL Server back end, and table level validations are not at all the same thing as
    table lookups in Access. As near as I can figure, the table lookups were added because Access is still marketed as an end-user product. In fact, end-users
    shouldn't be allowed near tables, so there isn't any reason for table lookups except laziness on the part of the developer. The rule of thumb in programming
    is to declare variables and objects just before you need to use them and to destroy them as soon as possible. Creating table lookups is kind of like declaring global variables for use at when the application closes. If you need lookups in a form, build the lookups in the form. Most of your validations should be done at the form level anyhow unless you've got a database server back end, because that's the only place you can really manage the validation with any kind of finesse.

    In my experience, there is a performance hit with table level lookups, especially in Access 2000. What appears to happen is that an implied relationship is created between the tables, and that can create unexpected subdatasheets in Access 2000. Subdatasheets are another idea that looks good and causes more problems than it could ever solve. I've seen them cause huge delays in just opening a linked table in the database window because of the internal
    queries being run.

    There isn't anything "native functionality" about table lookups. They're grafted on, and in my opinion they were grafted onto the wrong place to start with.
    The fact that they're there doesn't mean they're a good idea. Another "native" feature of Access 2000 is Name AutoCorrect, and the evidence is pouring in
    that implicates that little item in all kinds of apparently unrelated Access problems.

  2. #2

    Re: Lookups & Referential Integrity

    So along the same lines as table lookups, what is your experience with referential integrity? Use RI (with/without cascades as needed) or use form level data validation rules?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Lookups & Referential Integrity

    They serve different purposes. Referential integrity is enforced at the table level on a per record basis. It prevents you from creating "orphaned" records in one table by insuring that a related record exists in another. It also will cascade keys using cascade update and will automatically remove child records if you use cascading deletes. Referential integrity applies whether you use forms, queries, or type data directly into the table.

    Form level validation is usually at the field level, although it also allows you to test multiple fields in the same record to see if the values are complete and compatible before it allows you to save the record. Form level validation doesn't know about referential integrity, and it only applies when a form is open. However, the form allows you to handle validations much more gracefully than at table level, since you can error trap the form's event procedures.

Posting Permissions

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