Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    Amarillo, Texas, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reseting Autonumber (2000)

    I am using a database as a testing tool for data. I have a table that I am using to define how many text boxes are visible based on the number of entries in this table. I am also using the Key field for a criteria field in several queries. The Key is an autonumber. The first time I import data, the numbering starts at 1. This is good. ? <img src=/S/joy.gif border=0 alt=joy width=23 height=23> When I delete the data in the table and import new data, the numbering picks up where the old data left off. This is bad. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> I need to reset the autonumber to start over when new data is imported. I found that if I compact the database when the table is empty, the autonumber resets. This is good. <img src=/S/joy.gif border=0 alt=joy width=23 height=23> I was hoping to find a way to do this without compacting each time.
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Am I limited to the compacting? <img src=/S/question.gif border=0 alt=question width=15 height=15>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    Yes and no.
    The only way to reset an autonumber, is compacting.
    What you can do, is delete the table and recreate it with the SQL statement Create Table or with the CreateTableDef Method.
    Francois

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Location
    Amarillo, Texas, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    Thats what I was afraid of. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>
    Thanks for your response.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    Nothing to be afraid for. A little bit of code and you'll be so happy once it work <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Francois

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    A second thought.
    Instead of recreating a new table, you could have an empty table and after deleting the old table, just make a copy of the empty table.
    Coding would be simpler.
    Francois

  6. #6
    New Lounger
    Join Date
    Aug 2001
    Location
    Amarillo, Texas, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    That is a good idea. I could create a "template" table for any table where this would apply.

    Thanks

  7. #7
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    I did this in Access 97:

    1. Delete the Relationship, if any, with another table.
    2. Delete the designation of the autonumber as the key field and delete the data.
    3. Create another table with one field as a number field and as Long Integer format.
    4. Put in a value of 0.
    5. Append that table to the original.

    Let me know if it works for 2000.

    Soc

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    Soc,
    In a quick manually test, it seems not to work.
    If you do this by code, could you attach the code so i can test it ?
    Anyway in your logic you have to add a point 6 to remove the appended record with the 0 value.
    Francois

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    A variation on the theme

    1. Delete the Relationship, if any, with another table.
    2. Delete the autonumber field
    3. Add the autonumber field
    4. Recreate the Relationship

  10. #10
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    From MS Access 97 Help File:

    Change the starting value of an incrementing AutoNumber field

    For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

    1 Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.

    How?

    2 In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
    3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

    How?

    Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.

    4 Delete the temporary table.
    5 Delete the record added by the append query.
    6 If you had to disable property settings in step 3, return them to their original settings.

    When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

    Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

  11. #11
    New Lounger
    Join Date
    Aug 2001
    Location
    Amarillo, Texas, USA
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reseting Autonumber (2000)

    Francois

    I created a template table. Created a macro to:
    1) deleteobject to delete the old table
    2)copyobject to copy the template table with the old table name
    3)transfertext to import the data into the new table
    4)assign the macro to a command button and voila.

    Works everytime. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Thanks

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reseting Autonumber (2000)

    You'd better set the database to compact automatically on close then. Deleting and copying tables causes the database to bloat significantly if you don't keep compacting it.
    Charlotte

Posting Permissions

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