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!

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.

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..

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.

:)

Is this more or less what your looking for:

Quote:

---

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

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!