Results 1 to 11 of 11
  1. #1
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25

    Key violation when appending records

    Hi,
    I am currently getting a very odd key violation when appending values to a table. The strangest thing is that it is only happening some of the time.

    I have a form where people can create schedules. They can create single day events or they can select events that happen every Tuesday from today until the last day of September for example. Their schedule gets parked into a temporary table where they can then edit any details (for deleting bank holiday dates, etc.,) and they then click a button to append the data to the schedule table.

    When I do single-day events they append fine, but with the multiple dates I am having some problems and can't work out why. This is because there seems to be no rhyme and reason. When I select dates that are on the same dates as ones already in the schedule table I sometimes get a 'key violation' error. This only happens sometimes. For instance I just chose 10 different dates to append, all which were already featured in the schedules table and only one had a key violation. I then tried adding the same dates again with different data and all 10 had key violations.

    In the schedules table the primary key is an autonumber. I have set the 'dates' field as Indexed Yes (Duplicates Ok) and made sure that all other fields are either not indexed or specify Duplicates Ok.



    Can anyone think what this problem could be? I am really at a loss at the moment. I keep testing to try and find the problem but just can't work out why exactly this error is coming up.

    Thanks!
    Russ

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Did you specified the value of the autonumber field when you insert records?
    If yes, that maybe the cause of the problem.
    If not, I don't see anywhere you described can cause 'key violation' .

  3. #3
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25
    I'm not trying to insert an autonumber, none of the tables involved have a primary key or a relationship with another table. It's all very confusing.

    I can now definitely say that it is a problem with the 'dates' field. I have changed the append query so that the only fields being appended are the 'date' field and one other and each time there is a key violation. If I append all data except for the 'date' field it appends file and if I only append the 'date' field it works fine, but as soon as I try and append the 'date' field along with any other field, be it a number, text or memo it has a problem.

    What the devil is going on? Has anyone experienced anything like this before?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    To eliminate corruption as a possible issue, import your db into a new, empty db and see if the problem follows.

  5. #5
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25
    Hey RuralGuy,
    Creating a new database, imported everything and still no luck.

    It's so strange how this is happening. I just tried to add 10 dates, and it appended 5 of them, with the other 5 key violations. All 10 dates I chose were already in the destination table. The only pattern was that the dates on Wednesday were appended and not the Tuesday dates. How does that make any sense?

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by slaterino View Post
    ...none of the tables involved have a primary key or a relationship with another table.
    This does not agree with what you stated in Post#1.
    PMFJI Weekend00. I should have stayed out for a while longer.

  7. #7
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25
    Yeah,
    Sorry the 'schedules' table does have a primary key which is just an autonumber.

  8. #8
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Quote Originally Posted by RuralGuy View Post
    This does not agree with what you stated in Post#1.
    PMFJI Weekend00. I should have stayed out for a while longer.
    What does PMFJI mean?
    ================================================== ====
    Slaterino,
    Are you sure there is no another no duplicate index?
    Maybe you can try to create a new table with same field as this one, but totally no index, save the original table with another name and rename the new table to the working name and then try again.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by weekend00 View Post
    What does PMFJI mean?
    Pardon Me For Jumping In.

  10. #10
    slaterino is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    25
    It now seems to be working fine. I deleted all the records in all the tables I had been working with, and hey presto, it was working after that.

    I'm not sure I'll ever understand Microsoft Access!

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks like this thread is ready for you to follow the link in my sig and mark it Solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need appending help?
    By asilva in forum Access
    Replies: 6
    Last Post: 02-17-2010, 03:53 PM
  2. Appending to a Combo Box
    By cotri in forum Forms
    Replies: 5
    Last Post: 01-28-2010, 02:58 PM
  3. append, Key violation
    By klapheck in forum Queries
    Replies: 0
    Last Post: 09-17-2009, 11:50 AM
  4. unable to append... key violation... message box
    By Coolpapabell in forum Access
    Replies: 4
    Last Post: 08-31-2009, 02:11 PM
  5. Appending Records to tables with relationships
    By RubberStamp in forum Import/Export Data
    Replies: 0
    Last Post: 12-14-2008, 06:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums