Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append query/ Auto number (Access 2000)

    I have created an append query that takes a value from one table and appends it to another table. When it appends to the second table, the primary key is an auto number which should generate the next number. Instead the auto number is resetting itself to start over at 1 and getting unique contraints error. The auto number is set to increment and is not random. Can you help. Thank you.

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

    Re: Append query/ Auto number (Access 2000)

    Are you including the primary key in your append query? That is the usual cause of problems like this. If not, it may be that your database is corrupt and the autonumber has been clobbered. In that case, try importing everything into a new empty database.
    Wendell

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

    Re: Append query/ Auto number (Access 2000)

    What version of Access 2000 and Jet 4.0 do you have? There have been problems with AutoNumber fields - see for example ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database.

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

    Re: Append query/ Auto number (Access 2000)

    If in fact the internal counter that establishes the next autonumber has been screwed up, as Wendell suggests, you should make sure you have the latest patches to Access and Jet. If you check my website (see below), you will find a couple of utilities (1 version uses DAO, 1 version uses ADO) which will reset the autonumber counter. You may find this easier.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append query/ Auto number (Access 2000)

    You are right on target. I have been reviewing your site. I ran the programs for Reset Autonumber. However, I am not getting a result. I was expecting a report in print preview mode but nothing has developed. Can you tell what I have done wrong. Thank you for your help.

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

    Re: Append query/ Auto number (Access 2000)

    You haven't done anything wrong. There isn't any report. The utilities just do their thing and then end.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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