Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95

    Unanswered: Reset Autonumber

    Hi,

    A friend of mine asked me if it is possible to reset autonumbering in a table. I said I'd ask it here . Point is, they have a working Access-app with a DB, but for some reason they want to (and have) erased the data from one table, leaving an empty table, ready to be filled with data again.

    However, they want to reset the autonumbering, letting the first record be '1' again (it now counts onwards from the last autonumber used, of course).

    I have no clue why, I didnt ask, but I am aware of the risks, Ill tell him that later.

    I KNOW its possible, theres something in the help but I cant figure out what they mean (Access(97) help is very crappy), so I was wondering if one of you l33t peeps could help me with a manual for dummies :s

    Thanks a bunch

  2. #2
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    copy the table using "structure only"
    delete org table
    rename new table to old name

    but be aware that you loose your relations
    you have to rebuild them

    have a look at google, maybe someone provides a code to reestablish the relations

  3. #3
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You need to create a temporary table with one field in it set as a long integer - enter a 0 as a value (or any value less 1 then the value you want the auto number to start at). Use an insert query to add the the value to the table, then delete the record just added. The next new record will start the auto number off again. See Access Help under aut number for the full details.

    You could also use a data definition query where you drop the column and then add the column again - look in the help files for ALTER TABLE and DROP

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you have deleted all records in the table, compact & repair should reset the autonumber back to the beginning.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    compact & repair on empty table works perfect
    i guess i know whom to ask my next questions ..

  6. #6
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by izyrider
    if you have deleted all records in the table, compact & repair should reset the autonumber back to the beginning.

    izy
    This kind of reply was what I was hoping for Thx izyrider

Posting Permissions

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