Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    112

    Unanswered: using auto numbers again

    I have an access 2000 db with tons of tables! I use the auto number feature in many of them to give records individual id's. As soon as a user enters any data into one of these fields access allocates an auto number, unfortunatly if a user decides to delete the record that auto number is lost. is there a way so that the next user can use the lost auto number / re use auto numbers??

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    No there is not.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good answer, M

    saltman, yes, there is, but it is horribly complex and i am not going to tell you what it is

    autonumbers used as primary keys need to be unique, and that's all

    if you use them for anything else, then your design is flawed

    sorry, but that's the way it is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Posts
    112
    Thought as much, Thanks anyway.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by r937
    good answer, M

    saltman, yes, there is, but it is horribly complex and i am not going to tell you what it is

    autonumbers used as primary keys need to be unique, and that's all

    if you use them for anything else, then your design is flawed

    sorry, but that's the way it is

    Rudy,

    Hmmmm ... Now I'm interested ... How complex? We both know that you cannot DIRECTLY set the # in an autonumber column so, how do you indirectly do it? (Or is this another 1 of those fakeout tricks) ...

    Edit: Let me guess ... Copy data into same structure except for no autonumber column (converted to say a Long). Twiddle the # to your hearts content. Create new table with autonumber column. Copy records in and go your merry way ... Sound about right?
    Last edited by M Owen; 09-09-04 at 09:23.
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that'd be one way, here's how i'd do it --

    ctrl-c/ctrl-v the table

    (ain't it great how you can do that in access?)

    delete all the rows from the original table

    compact and repair the database

    insert/select the rows from the copy, without selecting the autonumber
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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