Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set the ID back to 1 in a Table (Access 2000)

    I have used SQL to delete the records in a table. How do I get the ID to be set back to 1 after I do this?

    Mike

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    The dirty way? Create another autonumber field, delete your current one, and rename the one you created...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    I need this to be done in VBA.

    Mike

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    Okay, perhaps <post#=358,042>post 358,042</post: > can help you out...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    Is your ID field an AutoNumber field? If so, the question is why you would want it to start at 1. An AutoNumber field is basically a unique but meaningless identifier, it doesn't really matter where it starts. However, you can make the AutoNumber restart after deleting records by executing an append query that adds one record with the new start value. This query can be run from VBA, if you like.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    I guess my real question then should be, is there a maximum number that the autonumber ID can be. If so, I am having to add and delete so much data that the autonumber ID may reach any max number in a relatively short time frame.

    Mike

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    An AutoNumber field is a Long Integer, it can go to 2,147,483,647 (if forget if it turns negative after that). That's pretty high!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    In regard to Hans' response, yes, it does turn negative after that. BTW, that's over 4 billion unique IDs - if you exceed that you can use a GUID as an id, though it's not recommended for performance purposes. Another strategy is to set the ID to a random value - that way when you delete records, you don't care about starting at 1 again. The whole idea however, is not to have any duplicate numbers at a given point in time - once your records are deleted, those numbers are free to be used again.

    Also, if you are adding and deleting records in large quantities, you may want to look at a more robust back-end. I've done as many as 4 million records in an Access database in the process of building an application, but for performance purposes, we normally store that kind of quantity of records in a SQL Server or Oracle back-end. Hope this provides some perspective on the situation.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    Unfortunately, doing the logical steps of using a stronger backbone will not work for me. (Do not ask!) How do you do the random number in the ID thing?

    Mike

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    If you open the table in design view and click in the AutoNumber field, you'll see the New Values property in the lower pane of the window. The default is Increment (see screenshot), but you can select Random from the dropdown list.

  11. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set the ID back to 1 in a Table (Access 2000)

    That works perfectly.

    Thanks
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •