Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Question Unanswered: delete auto increment

    I have an auto increment field in my database, when I delete a row of data my auto increment field becomes out of sequence. Is there any way to re-order this field so that it becomes in sequence again.

    example: if I have the following

    1 fred red
    2 tom green
    3 joe blue
    4 jan yellow

    and I delete the third record, it becomes:
    1 fred red
    2 tom green
    4 jan yellow

    how do I get it to look like this:
    1 fred red
    2 tom green
    3 jan yellow

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The autonumber doesn't reset because it is indexed. Once it has been entered, the index is reserved whether the record is deleted or not. If you check out this link you should get the answers you need. http://support.microsoft.com/default...roduct=acc2000
    Sorry to use a link but it's a bit long winded to type all the steps. Which is the main problem - I don't know what anyone else thinks but the more I deal with auto-numbers the less I like them. They have no real meaning, other than the order records were entered. They can be of some use as an alternate key but otherwise I'd stear clear. If you do use them and need them to always be consecutive, bear in mind that you'll either have to go through the above steps each time there is a deletion or you will have to prevent users from deleting. Neither is generally a good option. For my last penny worth on the subject, I've had corruptions where an autonumber jumps inexplicably from 3000 ish to 2987363912 (again approximate). Sorry to go on - bit of a bee in my bonnet about auto-numbers

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    thank you for your reply, is there a way to do this using only SQL statements??

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Drop the Feild save the database the insert it again

    only if you don't have relationship setup
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why do you need it to look that way? If you just need a rowcount, there's much better ways to do that.

    Otherwise yeah, you have to delete the field and re-add it.

    However, there's no practical reason for you to need to do this.

  6. #6
    Join Date
    Sep 2003
    Posts
    5
    I have tried to delete the auto increment column, but it will not delete. I have it set as the primary key, is this why??

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    NEVER CHANGE THE PRIMARY KEY. EVER.

    What is the point of using access at all if you're going to monkey around with the primary key? You're better off using excel.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    AutoNumber SQL

    Hi

    if you are going to persevere with autonumbers and need them consequtive then I guess doing it in vb is better than deleting the field and saving the database (although I like that more than Bill Gates' long winded way).

    Try putting this against an event
    ------------------------
    DIM STRsqL as string

    strSQL = "create table YourNewTableName (Field1 varchar (30), Field2 integer, Field3 AutoIncrement)"

    currentdb.execute strsql, dbfailonerror

    'Then:
    strSQL = "insert into YourNewTableName (Field1, Field2) select YourOldTableName.Field1, YourOldTableName.Field2 from YourOldTableName".
    'The main thing here is to append all values apart from your AutoNumber.

    currentdb.execute strsql, dbfailonerror

    'Then either
    DoCmd.Rename "ArchivedData " & date() , acTable, "YourOldTableName"
    'and the same again for your new table, or you could do:

    strSQL = "drop table YOUROldTableName"
    'instead of archiving it.

    currentdb.execute strsql, dbfailonerror
    ----------------------------------------------
    I think I've done this before - I can't see why it wouldn't work unless my SQL isn't quite right

  9. #9
    Join Date
    Sep 2003
    Posts
    5
    ok, I will try that.......thanks

Posting Permissions

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