Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Resetting Auto number ID column

Posted on 2008-06-15
4
545 Views
Last Modified: 2013-11-27
Greetings Experts,

I don't think this should be a difficult thing to do but I can't seem to figure it out: I have a table in an Access database and because of several script tests, I have a lot of test records that I've had to delete.   Even though I've deleted these test records, the autonumber ID column continues to assign the culmulative total of all records ever created i.e. I've run 17 tests, deleted all 17 records, run another test and the autonumber ID column starts number from 18.

How do I reset the autonumber column ID so it starts numbering records from 1 after running and deleting these test records?  I hope that was clear and thanks for the help!
0
Comment
Question by:max7
4 Comments
 
LVL 3

Assisted Solution

by:darkmooink
darkmooink earned 125 total points
ID: 21790173
the way i have done it in the past, although I don't believe it is the easiest or best way of doing it, is by deleting the column and then recreating it.
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 180 total points
ID: 21790385
To start autonumbering.
1 Create a new table
2 Append old table to new one
3 Delete old table
4 Rename new table to old name.
0
 
LVL 58

Accepted Solution

by:
harfang earned 195 total points
ID: 21790584
When you use (Tools | Database Utilities | Compact and Repair Database...), all empty tables get their counter reset to 1. In past versions, it would also reset non-empty tables to the highest value plus 1, but that is no longer true.

(°v°)
0
 
LVL 1

Author Closing Comment

by:max7
ID: 31467435
So it's not so straightforward after all - I don't feel as bad now!  Thanks to all of you for the excellent guidance.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question