Others

How to Reset an Access AutoNumber Field to Start at 1

Muhammad Talha Oct 9, 2026 869 views

Question details

The user wants to reset the AutoNumber field in an Access database so that new entries begin at 1, typically after copying a database and clearing old data.

How to Reset an Access AutoNumber Field to Start at 1

Product: Microsoft Access
Device & OS: not provided
Scenario: Copying an existing database for a new year or project and deleting the old records to start fresh.
Observed behavior: When new records are added, the AutoNumber field continues incrementing from the highest value of the previously deleted records instead of restarting at 1.

Before you start

Ensure you have a complete backup of your database before modifying tables, and verify that absolutely all records have been deleted from the target table to allow the counter to reset.

Solution 1Recommended

Reset AutoNumber Using the Compact and Repair Tool

Using the built-in Compact and Repair Database utility is the standard method for resetting the AutoNumber seed on a completely empty table.

This method will only work if the table contains zero records. If even a single record remains, the AutoNumber will not reset to 1 and will continue from the highest existing value.

Delete all existing records

Open your copied Microsoft Access database, open the target table in Datasheet View, select all records, and press the Delete key to remove them completely.

Close the table

Close the table by clicking the 'X' on the table tab. Ensure no other tables or queries referencing this data are currently open.

Run Compact and Repair

Navigate to the 'Database Tools' tab on the Access ribbon and click on 'Compact and Repair Database'. Once the process finishes, the AutoNumber field will be reset to 1 for the next new record.

Reset AutoNumber Using the Compact and Repair Tool

Verification: Open your table and enter a new test record. The AutoNumber field should now display '1'.

Solution 2

Create a Custom Long Integer Field for Business Numbering

If you need a sequential number that resets yearly (like an invoice number), you should use a custom Long Integer field instead of relying on AutoNumber.

Free Microsoft Office alternative

Discover a Lightweight and Free Office Alternative

While Microsoft Access is used for complex database management, WPS Office is the perfect lightweight alternative for handling all your daily document, spreadsheet, and presentation tasks. With WPS Spreadsheet, you can easily manage flat-file databases, filter data, and track numbered lists without complex database software.

1. Download and Install: Visit the official WPS website to download the free installation package for your operating system.
2. Open Your Data Lists: Launch WPS Spreadsheet to open your existing .xlsx or .csv data exports from Access or Excel.
3. Manage Data Easily: Use built-in data forms, auto-numbering drag tools, and advanced filtering to manage your business records effortlessly.

Manage flat-file database lists and track sequential data easily with WPS SpreadsheetFully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formatsCompletely free, lightweight, and consumes fewer system resourcesFamiliar tabbed user interface allows for seamless and instant migration

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use AutoNumber for my invoice or order numbers?

AutoNumber fields are designed strictly by the database engine to provide unique primary keys for records. They do not guarantee a gapless sequential order (numbers can be skipped if a record creation is cancelled) and cannot be easily customized to reset based on business rules, such as a new calendar year.

Why didn't the Compact and Repair tool reset my AutoNumber field?

The Compact and Repair operation will only reset the AutoNumber seed back to 1 if the table is completely empty. If even one single record is left in the table, the AutoNumber will continue incrementing from the highest existing value.

Is it a good practice to copy and create a new database file every year?

Generally, no. Creating a new database every year creates data silos, making historical comparisons, cross-year reporting, and long-term data analysis much more difficult. It is better to maintain a single database and use date fields to filter records by year.

Office Quick Solutions