MS Access 2000 - How can I restart the "ID" autonumbering?

[Arschloch]

Hello.

The topic says it all. I designed a database and entered some trial data in order to test it out. I then cleared out all of the data. However, the ID number is at 13, and each time I enter more data and delete it, the ID increases again. Is there any way to get the ID to start over again at 1?

Thanks in advance.

-Arschloch

 

[FOBSIDE]

no you cant restart the autonumbering. if you delete entries, those numbers are gone.

 

[Arschloch]

Thanks for the tip. I was afraid of that. It shouldn't be TOO big of a deal though.

-Arschloch

 

[FOBSIDE]

yeah, when i first started using access for web programming i thought the same thing. you shouldnt use autonumber as an id to identify entries in the db.

 

[Mark R]

<< you shouldnt use autonumber as an id to identify entries in the db >>



That's precisely what autonumber is for. You use it when each entry needs a guaranteed unique ID - even if the database is shared between multiple users (e.g. on a web server), it still ensures that each entry is unique. Any other technique of generating new ids gets complicated if there can be multiple users modifing the database.

What you shouldn't use it for is to count items.

 

[FOBSIDE]

<<

<< you shouldnt use autonumber as an id to identify entries in the db >>



That's precisely what autonumber is for. You use it when each entry needs a guaranteed unique ID - even if the database is shared between multiple users (e.g. on a web server), it still ensures that each entry is unique. Any other technique of generating new ids gets complicated if there can be multiple users modifing the database.

What you shouldn't use it for is to count items. >>


youre correct. my mistake. i wasnt thinking clearly.

 

[Ben]

There is a way to reset it but it's kinda lenthly. This was pulled from the MSAccess help files. Use them, they are your friend.



<< 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.

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.

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.

Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

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.

Delete the temporary table.


Delete the record added by the append query.


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.
>>



I've done it and it works. But, it is complicated.

At the end of each year I have to restart the autonumber field for a database at work with the last two digits of the year. For example, on January 1, 2002 I have to restart the sequence at 020000. I just leave all the temp files there because, like I said, I have to do this once a year.

 

[coder1]

compact it under the tools section, that will work.