Zeroing out autonumber fields in tables 1

[buzcut]

How do I zero out the autonumber counter in tables to start the count all over again without deleting the table?

Thanks!

buzcut

 

[johnster]

Teh only way you can zero an auto number is to delete the field from the table and adding it again. give it exactly the same name again, give it a primaray key again if needed and re link the relationship if necessary. you may have to delete the relationship before you can delete this field.

 

[buzcut]

Am I slow or what? See what I mean? Thanks, John, you're the best. I thought that might be the way, but I wasn't sure.

Thanks again!
buzcut

 

[Guest_imported]

Well, AutoNumber fields in Access start at 1, not 0, but you probably know this.

If you don't mind losing the data, you don't need to delete the table definition or the field. To reset a field of type AutoNumber, delete all the rows in the table. Close the table. Then Compact the database (from the Tools menu). The next record you add to that table will get a 1 in the AutoNumber field.

If you want to keep the data that's in your table, you can Copy the table, then clear out the original table as explained above, and write a query to append all the fields from the copied table into the orignal table. Finally, drop the copied table (that is, delete it).

 

[JuniorWaterCzar]

You might want to take JustMeSD's response to the next level and make a macro to automate this task. I have found that when making up sample data for an application I am working on, this saves a bit of time.