Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Unanswered: Table Reset Autonumber Problem

    Using Access 2000

    In my table which contains 747 records I would like to reset the autonumber.

    I have followed the help directions to the letter and the append query places the record in the table like it should.

    The query places the appropriate new number in the table, yet when I create a new record the it assigns a new number based upon the OLD one and NOT what it should be.

    I.E. I have reset the autonumber to 1, the next one should be 2. But that is not working.

    Is there a bug or workaround?

    Jacque

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In some versions (I believe a2k to be one), compact & repair resets to one, else, if the autonumber field is not used in any relationships, then you can run the following sql (either as a stored query, docmd.runsql, currentdb.execute ...)

    ALTER TABLE MyTable ALTER COLUMN MyAutoNumberField COUNTER (1, 1)

    But - this indicates you're assigning a business meaning to the Autonumber, which is something seldom recommende. One would usually se recommandations of only using the Autonumber field as means of identifying a record, relationships between tables etc, and hide it to the users.
    Roy-Vidar

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Agree with RoiyVidar, that implicitly you are deriving a value form the autonumber. Often this happens with invoice / order numbers, where a bean counter / auditor / goverrment extortionist (AKA taxman) needs to se contiguous numbers.

    If you have this issue then either you have to generate your own primary key in place of the autonumber function- there are some usefull examples here if the search facility lets you see them. OR you have to provide the document trail to indicate why the missing numbers are not valid invices / orders - effectively you store a flag which indicates if its a live / valid record or transfer the old row to an deleted table. Either way its messy.

    Another way to reset the autonumber field is to delete it and then re-create it and all the associated references, which frankly is a pain. Ber in mind that an autonumber field is a system generated number - some database engines allow you to specify the start number, some allow you to specify the interval between numbers. As a system generated number it shouldn't matter what the value is providing you can have confidence that it is unique - its there to help the data storage and retrieval mechanisms not for human (neccesarily) for human usage.

Posting Permissions

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