How to restart counting from 1 after erasing table in MS Access?

Question

I have table in MS Access that has an AutoNumber type in field ID

After inserting some rows, the ID has become 200

Then, I have deleted the records in the table. However, when I tried to insert a new row, I see that the ID starts with 201

How can I force the ID to restart with 1, without having to drop the table and make new a new one?

Hunyadi

Whatever you're trying to accomplish by doing this (No idea why anyone would care what and ID number is as long as it is unique and you don't run out of them.), I'd look for a better solution. – Sperry

Answer 1

You can use:

CurrentDb.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)"

I hope you have no relationships that use this table, I hope it is empty, and I hope you understand that all you can (mostly) rely on an autonumber to be is unique. You can get gaps, jumps, very large or even negative numbers, depending on the circumstances. If your autonumber means something, you have a major problem waiting to happen.

Stansbury

This looks great, too bad it fails if there is a relationship using that myID column... – Dodie

@AlexisWilke Hence my comment above "I hope you have no relationships that use this table". You really should not care about autonumbers other than that they provide a unique reference. – Stansbury

Yeah, one of my customers is using the auto-number as their Order Number and after a little quirk, they have a gap... Can't fix, though, they have relationships! In my own coding, I use auto-numbers for relations (unicity), but not for numbers that are displayed to the end user. – Dodie

@AlexisWilke That should never be done, I cannot stress this enough. If you need a number that means something, build your own, eg #11950103 Domain aggregate functions (eg DMax) will get you in the end as well. – Stansbury

Answer 2

In Access 2010 or newer, go to Database Tools and click Compact and Repair Database, and it will automatically reset the ID.

Amalia

I actually much agree with this solution - a simple C+R will reset the autonumber. – Scrobiculate

Also works in Access 2013. This definitely seems to be the simplest, most foolproof method and it works with many relationships. I also know that the ID doesn't really matter but after bug testing my import script it's nice, albeit very anal, to be able to start from 1 again. :P – Satchel

Saved me! Simple and it works. Fixes when you deleted rows and your autonumber forms gaps in it. – White

Answer 3

I am going to Necro this topic.

Starting around ms-access-2016, you can execute Data Definition Queries (DDQ) through Macro's

Data Definition Query

---

ALTER TABLE <Table> ALTER COLUMN <ID_Field> COUNTER(1,1);

---

1. Save the DDQ, with your values
2. Create a Macro with the appropriate logic either before this or after.
3. To execute this DDQ:
   • Add an Open Query action
   • Define the name of the DDQ in the Query Name field; View and Data Mode settings are not relevant and can leave the default values

WARNINGS!!!!

1. This will reset the AutoNumber Counter to 1
2. Any Referential Integrity will be summarily destroyed

Advice

• Use this for Staging tables
  • these are tables that are never intended to persist the data they temporarily contain.
  • The data contained is only there until additional cleaning actions have been performed and stored in the appropriate table(s).
  • Once cleaning operations have been performed and the data is no longer needed, these tables are summarily purged of any data contained.
• Import Tables
  • These are very similar to Staging Tables but tend to only have two columns: ID and RowValue
  • Since these are typically used to import RAW data from a general file format (TXT, RTF, CSV, XML, etc.), the data contained does not persist past the processing lifecycle

Edieedification

Answer 4

In addition to all the concerns expressed about why you give a rat's ass what the ID value is (all are correct that you shouldn't), let me add this to the mix:

If you've deleted all the records from the table, compacting the database will reset the seed value back to its original value.

For a table where there are still records, and you've inserted a value into the Autonumber field that is lower than the highest value, you have to use @Remou's method to reset the seed value. This also applies if you want to reset to the Max+1 in a table where records have been deleted, e.g., 300 records, last ID of 300, delete 201-300, compact won't reset the counter (you have to use @Remou's method -- this was not the case in earlier versions of Jet, and, indeed, in early versions of Jet 4, the first Jet version that allowed manipulating the seed value programatically).

Rozella

Answer 5

I think the only ways to do this is outlined in this article.

The article explains several methods. Here is one example:

To do this in Microsoft Office Access 2007, follow these steps:

Delete the AutoNumber field from the main table.

1. Make note of the AutoNumber field name.
2. Click the Create tab, and then click Query Design in the Other group.
3. In the Show Table dialog box, select the main table. Click Add, and then click Close.
4. Double-click the required fields in the table view of the main table to select the fields.
5. Select the required Sort order.
6. On the Design tab, click Make Table in the Query Type group. Type the new table name in the Table Name box, and then click OK.
7. On the Design tab, click Run in the Results group.
8. The following message appears:

   You are about to paste # row(s) into a new table.

   Click Yes to insert the rows.
9. Close the query.
10. Right-click the new table, and then click Design View.
11. In the Design view for the table, add an AutoNumber field that has the same field name that you deleted in step 1. Add this AutoNumber field to the new table, and then save the table.
12. Close the Design view window.
13. Rename the main table name. Rename the new table name to the main table name.

Karat

Answer 6

I always use below approach. I've created one table in database as Table1 with only one column i.e. Row_Id Number (Long Integer) and its value is 0

INSERT INTO <TABLE_NAME_TO_RESET>
SELECT Row_Id AS <COLUMN_NAME_TO_RESET>
FROM Table1;

This will insert one row with 0 value in AutoNumber column, later delete that row.

Holily

Thanks. And I've seen that it works also with tables with many lines INSERT INTO table_target (myid, column2, column3) SELECT Row_Id AS myid, column2, column3 FROM table_source_with_many_rows order by myid; – Angele

But with many rows (14 for example) you have to reset myid by hand after insert with ALTER TABLE yourTable ALTER COLUMN myID COUNTER(15,1) – Angele