Results 1 to 6 of 6

Thread: MS Access restart autonumber

  1. #1
    Banned
    Join Date
    Aug 2001
    Location
    Yes
    Posts
    4,424

    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!

  2. #2
    Senior Member Raion's Avatar
    Join Date
    Dec 2003
    Location
    New York, New York
    Posts
    1,299
    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!

  3. #3
    Senior Member
    Join Date
    Jan 2003
    Posts
    3,915
    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..

  4. #4
    Senior Member nihil's Avatar
    Join Date
    Jul 2003
    Location
    United Kingdom: Bridlington
    Posts
    17,188
    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.


  5. #5
    The ******* Shadow dalek's Avatar
    Join Date
    Sep 2005
    Posts
    1,564
    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

  6. #6
    Banned
    Join Date
    Aug 2001
    Location
    Yes
    Posts
    4,424
    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

  1. Windows Error Messages
    By cheyenne1212 in forum Miscellaneous Security Discussions
    Replies: 7
    Last Post: February 1st, 2012, 02:51 PM
  2. Port List
    By ThePreacher in forum Miscellaneous Security Discussions
    Replies: 17
    Last Post: December 14th, 2006, 09:37 PM
  3. Securing 2000 Pro
    By akachuckie in forum The Security Tutorials Forum
    Replies: 8
    Last Post: February 24th, 2005, 01:47 AM
  4. Tcp/ip
    By gore in forum Newbie Security Questions
    Replies: 11
    Last Post: December 29th, 2003, 08:01 AM
  5. The Worlds Longest Thread!
    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
  •