Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65

    Unanswered: Debugging query errors

    Hi!

    How do you debug query errors? When I do an append query on my tables I get this error:



    quote:
    --------------------------------------------------------------------------------
    Microsoft Access set 0 fields to Null.snip.. and it didn't add 14 records to the table due to key violations,...snip... and 660 records due to validation rule violations.
    --------------------------------------------------------------------------------



    How can I make the program indicate which part of the record causes the errors? I remember when I export data I get a table for errors ubt what about queries? Is there a way I can make it to log which record and which field is causing the problem?

  2. #2
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    In a word, no. The data validation is all done at engine level and there's precious little you can do to influence its reporting. In your particular case, the error has arisen because you are trying to duplicate primary key values in your destinations table: in other words the data you are trying to append contains some key values that already exist in the table you are appending to.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    So if I want to exclude records existing in both tables how should I rewrite my append query?

  4. #4
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    In the Key field for your query set its criteria to :

    Not In(SELECT MyKey2 FROM MyTable)


    where MyKey2 is the name of the key field in the destination table and MyTable is the name of the destination table. This excludes records from your source table where the key values already exist in the destination table.
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    What if the table is from another database file? How can I reference it in my query?

  6. #6
    Join Date
    Sep 2003
    Location
    Caldes de Malavella, Spain
    Posts
    244
    It makes no difference. Just add it into your query and treat it like any other table (I assume you're talkiing about the source table)
    Andy Briggs
    Elmhurst Solutions Limited
    Database Development and Consultancy
    http://www.elmhurstsolutions.com

Posting Permissions

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