Reset Autonumber values in Access DB
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=8843
Printed Date: 30 March 2026 at 7:36am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Reset Autonumber values in Access DB
Posted By: theSCIENTIST
Subject: Reset Autonumber values in Access DB
Date Posted: 13 January 2004 at 4:42am
|
I have WWF running out an Access DB, I have made changes over the years to it and added many new fields to store all sorts of information, I am now cleaning up the forums and will start from the beginning with no users, categories, forums, topics and threads. The problem is that the auto-number fields still add the IDs to a higher value and not from 1,2 and so on, ie. last user had an ID of 480, all users are erased, but for every new user added, it gets 481, 482 and so on, how can I reset this to start from the beginning? Does anyone knows how to do reset the database AutoNumber fields on Access DBs? |
Replies:
Posted By: aalavar
Date Posted: 13 January 2004 at 8:26am
|
I've always had the same problem in access... If you change the field to a number though, you can query the database and get the maximum value, then add 1 to it, then insert using that number, but I don't think that it will work with the autonumbers. |
Posted By: zaboss
Date Posted: 13 January 2004 at 8:34am
|
Just rename the old autonumbered field to something else, make it instead of auto-numbered to number and set a new auto-numbered field with the old one's name. Set it as primary key and incremental and it should start with 1 Although sometimes for me it starts with 22, I never figured out why ------------- Cristian Banu http://www.soft4web.ro - Soft 4 web |
.
.Posted By: Mart
Date Posted: 13 January 2004 at 11:42am
|
it starts with 22 because you delete previous values. The autonumber is incremented everytime regardless of earlier values. E.g. You have a table with 3 records in and an ID column going from 0 to 2. if you delete record 2 when you insert the next record it will not replace record 2. E.g. the ID column now looks like [ID] ---- 0 1 3 |
Posted By: zaboss
Date Posted: 13 January 2004 at 11:45am
|
Nope. I do create a brand new table, so there are no previouse deleted record. And, sometimes, it does not start with 1 as it is supposed to be, but with 22. And everytime it starts with other number but 1 it starts with 22. Not 23, not 45, not whatever, but only 22. ------------- Cristian Banu http://www.soft4web.ro - Soft 4 web |
Posted By: theSCIENTIST
Date Posted: 14 January 2004 at 4:25am
|
So I need to rename the AutoNumber fields and create a new ID AutoNumber, I'm assuming that after that I can delete the old renamed field. I have to repeat the process to all IDs AutoNumbers, isn't there another way to quickly reset all this fields at database level? |
Posted By: michael
Date Posted: 14 January 2004 at 9:32am
|
Following is the information about how to change the starting value of an incrementing AutoNumber field in Microsoft Access Help. Hope it helps! 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. How? 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. How? 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. ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: psycotik
Date Posted: 14 January 2004 at 7:15pm
| If you go changing field names and types watch your links. You might stuff up integrity. |
Posted By: engla001
Date Posted: 04 February 2004 at 11:37pm
| If you will run the Compact and Repair Database tool AFTER you have dumped everything from the database it will reset the Autonumbering. However, if you do not dump all user accounts - including the admin accounts - the autonumber will reset in all tables except tblAuthor. |
Posted By: Phat
Date Posted: 05 February 2004 at 1:13am
|
If you're making a whole new database just make a blank db then use the
import tool to import the structre from the other database. Just make sure you only import the structue and not the data. File > Get External Data > Import > Seelct you database Now Click the options button there is an optionin there for definition and data or just definition |