-
February 2nd, 2007, 08:50 PM
#1
MS Access restart autonumber
I'm writing a C# application with an Access database behind it, and I stumbled upon something I can't figure out: one of my table's Primary Keys is in AutoNumber format, and I would like the auto-number to start from whatever the last number present is. It does not do that (it starts from whatever the last number EVER present was...).
For example:
Table contains:
1 - stuff
2 - more stuff
3 - even more stuff
If I delete record 3, then add another record, the table looks like this:
1 - stuff
2 - more stuff
4 - new stuff
rather than
1 - stuff
2 - more stuff
3 - new stuff
I can't even figure out how to reset the auto-number count from within Access, let alone from my code...
Anyone have any suggestions?
I realize that I could get rid of the AutoNumber type and programmatically take care of the numbering - it seems to me like there should be a simple and effective solution to reset the AutoNumber, though...
Thanks!
-
February 3rd, 2007, 07:17 AM
#2
I certainly can't answer your question, but if you do get to the root of this evil created by Access it would be greatly appreciated to let us know; I find it pretty illogical as well.
WARNING: THIS SIGNATURE IS SHAREWARE PLEASE REGISTER THIS SIGNATURE BY SENDING ME MONEY TO SEE THE COMPLETE SIGNATURE!
-
February 3rd, 2007, 07:54 AM
#3
Hey Hey Neg,
I actually don't think there is... It's pretty standard in databases to continue on with the Autonumber scheme (many of the *real* databases do the same thing)...
My opinion is that it was born out of laziness..
Person A: Now what will people use databases for..
Person B: Most applications will probably include user accounts... Something along the lines of UID (AutoNumber), UserName, Password, etc...
Person A: Excellent... UID will be the Users ID and a great way to link to other tables...
Person B: But what if they delete a user... they'll have to delete all related entries as well... That could be tedious.
Person A: Well if we make AutoNumber continue counting up then we won't have to worry about it
Person B: The lazy mans approach... I Like it..
-
February 3rd, 2007, 09:56 AM
#4
I can only speak as far as v 10 (Office 2002) but HT~ is spot on. If you think about it, the autonumber is pretty meaningless? It is used to ensure that every record has a single unique key, thus maintaining the integrity of the relationships.
If you want to re-use a number then you have to replace the data rather than delete it. In other words edit the existing record.
The logic is that the database uses a static history rather than a dynamic one. If you want dynamic, you need to create your own key to accommodate it.
-
February 3rd, 2007, 03:40 PM
#5
Is this more or less what your looking for:
SUMMARY
This step-by-step article describes how to reset an AutoNumber field value in Access. The AutoNumber field value in Access does not automatically reset when you delete some rows or all rows in a table. To reset the AutoNumber field value and to refresh the AutoNumber value in the referenced table, you must manually perform some tasks.
http://support.microsoft.com/kb/812718/en-us
PC Registered user # 2,336,789,457...
"When the water reaches the upper level, follow the rats."
Claude Swanson
-
February 3rd, 2007, 05:48 PM
#6
I'm indeed afraid HT is right
What Dalek posted works, of course, but that article is basically telling you to delete the field and recreate it - I could do that programmatically, but I think I'm just going to leave it like it is.
Thanks all - it's appreciated!
Similar Threads
-
By cheyenne1212 in forum Miscellaneous Security Discussions
Replies: 7
Last Post: February 1st, 2012, 02:51 PM
-
By ThePreacher in forum Miscellaneous Security Discussions
Replies: 17
Last Post: December 14th, 2006, 09:37 PM
-
By akachuckie in forum The Security Tutorials Forum
Replies: 8
Last Post: February 24th, 2005, 01:47 AM
-
By gore in forum Newbie Security Questions
Replies: 11
Last Post: December 29th, 2003, 08:01 AM
-
By Noble Hamlet in forum AntiOnline's General Chit Chat
Replies: 1100
Last Post: March 17th, 2002, 09:38 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|