Results 1 to 6 of 6
  1. #1
    rayc is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    Dec 2005
    Location
    Montreal, Canada
    Posts
    19

    duplicate autonumber

    I recently read article Q291162 that explains a bug in Access 2002 about autonumber fields not generating the correct next number. After I compacted the database, I had that problem where my highest id was 12677 and when I added a new record, Access assigned 267 as the next number. This article says that the problem was fixed in the latest service pack....I have the latest service pack and I still have the problem.

    I have version 4.0.8618.0 of the culprit file (msjet40.dll)



    Help!

  2. #2
    BCJourney is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Cincinnati, OH
    Posts
    46
    I would try to reset the autonumber. To do this you have to delete all records. Export the table to excel, then delete all the records but keep the table. Do a compact and repair. Close and re-open the databse, then import your data back in.

  3. #3
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Weird.... I just came across the same thing in Access 2010... All of a sudden my master table, which had a id field set to autonumber starting generating id number in the middle of the existing number scheme......!!!!

    CementCarver

  4. #4
    BCJourney is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2012
    Location
    Cincinnati, OH
    Posts
    46
    I'm at a loss, sorry I don't have anymore suggestions.

  5. #5
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    After some research, there seems to be several options via the internet and one of them is Allen Browne's code to reset the autonumber seed number.

    http://allenbrowne.com/func-ADOX.html#ResetSeed

    Has anyone tried this? What are your comments?

    CementCarver

  6. #6
    CementCarver's Avatar
    CementCarver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Location
    Canada
    Posts
    231
    Just finished applying one of Allen's suggested steps to remedy this error, by compacting my 2010 Access database and this fixed my autonumber problem.

    CementCarver

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

Similar Threads

  1. Duplicate command button
    By brettg in forum Database Design
    Replies: 1
    Last Post: 08-04-2008, 04:16 AM
  2. AUTONUMBER
    By J A F F A in forum Database Design
    Replies: 1
    Last Post: 10-03-2007, 10:30 AM
  3. Duplicate add to new table
    By horseyshoe in forum Access
    Replies: 3
    Last Post: 09-01-2006, 07:51 AM
  4. Which duplicate?
    By grgerhard in forum Import/Export Data
    Replies: 1
    Last Post: 05-27-2006, 06:19 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