Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: Autonumber resetting problem

    I posted this question in the wrong forum and reposted it here.

    Hi,

    I am running Access 2002. I have a table with a column set to data type of autonumber. This table is deleted and reloaded. I want to have the autonumber column to start at one everytime I am reloading. This is what I have done:

    1. delete the rows from the table
    2. run a query that will insert a row where the value of the autonumber column is o
    3. delete the row just inserted
    4. run query to insert row to the table without the autonumber column
    5. the autonumber column starts with the next number after the last row of data instead of 1

    I don't know what I am doing wrong. Any suggestions?

    Thanks.

    P.S. even though database is created in Access 2002, when the db is opened, it says Database (Access 2000 file format). Don't know if this is important.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi cte120907

    Welcome to the forum

    Would you mind awfully putting a note on your duplicate posts to that effect please?

    There are a few methods of reseeding (that is the word you were looking for) autonumbers. One of them is to compact and repair your db after deleting the rows. Another is some DDL:
    Code:
    ALTER Table AutonumberExample ALTER AutonumberCol AUTOINCREMENT(1, 1)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    Will do.

    Regarding the reseeding of the autonumber, I don't want to compact and repair the db every time I want to reseed the table. I followed the instruction in Help reseed but it does not work. Any other suggestions?

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did you try the DDL code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Let's see ... Assumptions: AutoNumbered table. Emptied each time. I say, screw it. Drop the table and create it new each time ... Unless the table is related to others ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Unless the table is related to others ...
    And then you'd be happy with a reseed?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    And then you'd be happy with a reseed?
    Reseeding ... Dropping ... Making new ... Replacing ... Who cares as long as it gets done ... As you and Izy WELL know, I don't use Autonumbers as primary keys. I use them only in tables that are temporary ... Caue I don't want to have to deal with specific issue ... Avoidance works too!
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Aug 2006
    Location
    Thailand
    Posts
    12
    Pradon in my English and for I am a Newbie but interest:
    May this help?
    http://support.microsoft.com/default...n-us%3BQ209696
    Last edited by polngpole; 09-02-06 at 11:17.

  9. #9
    Join Date
    Aug 2006
    Posts
    4
    To pootle flump:
    The DDL works. I will use that instead of my query to reseed.

    To polngpole:
    My query essentially does what MS said to do.

    Thanks for all the help.

Posting Permissions

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