Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation Rule violation with no validation rule? (Access 97SR2)

    A colleague has just brought this question to me and I'm stumped. I'm not sure of the purpose of the database, but I don't think it matters in the context of the problem.

    We have a source table called "TABLE1"
    We have an append query called "QUERY1"
    We have a destination table called "TABLE2"
    All QUERY1 does is append the data in TABLE1 to TABLE2, making no changes along the way (I beleive it used to make some changes, but due to the error listed below, all criteria and formulae were stripped from it so now it just appends all of the data from one to the other)
    We start out with 775 records in TABLE1 and no records in TABLE2 and run QUERY1 and end up with 775 records in TABLE2
    If we delete the contents of TABLE2 and run QUERY1 again, we end up with the same result, time and time again
    If we delete the contents of TABLE2, then open TABLE2 in design view and click the save button without changing anything at all, then close TABLE 2 and run QUERY1, we get the following error message:
    "Microsoft Access didn't add 775 record(s) due to validation rule violations"
    TABLE2 contains no validation rules. Most fields are text 255 chars, and most have Allow Zero Length set to No, but the QUERY1 makes an entry in every field for every record.

    Can anyone explain what is going on here?

    Thanks in advance, any help appreciated.

    Regards,
    JP

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Validation Rule violation with no validation rule? (Access 97SR2)

    Admittedly odd. Once you get the error, are you able to make it start working again? And how?

    The zero-length string setting could still be a problem, if the field in query1 had a zero-length; but I don't know why this would all of a sudden become a problem.

    How was this table initially created? Was it imported?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Feb 2001
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rule violation with no validation rule? (Access 97SR2)

    Hi Mark,

    Yes, we can get it working again, but only by deleting and re-creating TABLE2, and then it fails after we save the table design without changing anything. TABLE2 was originally an Excel spreadsheet, I believe, however the same behaviour was exhibited when we copied the structure only of TABLE2, and tried appending to that after viewing the table design and saving the table without changing it.

    Thanks in advance for any help

    Regards,
    JP

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation Rule violation with no validation rule? (Access 97SR2)

    How many fields are there in TABLE2? You can get some pretty weird messages if there are a large number of fields combined with 255 widths.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Validation Rule violation with no validation rule? (Access 97SR2)

    Quite frankly, I don't know what to tell you. It is certainly strange behaviour, and I can't think of what could be causing the problem. It doesn't seem logical that a validation error wouldn't show up after creating the table, but only after re-opening the table in design mode then saving. The only thing I can suggest is to manually read the input records and then add them 1 at a time to the problem table, to find out if it is a particular record that is causing this.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Validation Rule violation with no validation rule? (Access 97SR2)

    It looks like you are sort of stuck. Have you tried a compact and repair to see if something in the database structure is hosed? If you have, try importing the two tables into a brand new database - sometimes that will fix the strangest things. There is probably a logical answer to this question, but why are you switching to design mode and then saving the table? Normally you don't switch to design mode and then save unless you have made a design change. Is it true that you don't get any error messages as long as you don't try to save in the design mode?
    Wendell

Posting Permissions

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