Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    SK, Canada
    Posts
    9

    Unanswered: AutoNumber Question

    MS Access seems to have only two options; incremental and random?

    When you delete the record, the autonumbering system simply deletes the record but keeps the number sequence the same?

    Is there anyway for an 'autonumbering' system to update the records and re-assign (look up) the missing numbers and re-sequence the records?

    Not a big problem, but I have eliminated a whack of old records, as well as placed some test data in an existing database and then removed it?

    Can the sequencing be re-numbered?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Absolutely not.

    And you absolutely should not EVER do that.

    You're talking about re-assigning primary keys, which is detrimental to the integrity of a database.

    If you just need record counts or what-have-you, there are better ways to do that.

    So,
    GOLDEN RULE - NEVER CHANGE PRIMARY KEYS

    Now, if you don't have ANY data related to the table in question, you may remove the key column, and then re-add it. That's the only way access allows you to "reseed" the auto-inc number.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Absolutely not.

    And you absolutely should not EVER do that.

    You're talking about re-assigning primary keys, which is detrimental to the integrity of a database.

    If you just need record counts or what-have-you, there are better ways to do that.

    So,
    GOLDEN RULE - NEVER CHANGE PRIMARY KEYS

    Now, if you don't have ANY data related to the table in question, you may remove the key column, and then re-add it. That's the only way access allows you to "reseed" the auto-inc number.
    Ted,

    An autonumber does not need be a primary key ... As for re-sequencing, the only way to do that is to copy the structure to another table name and then copy the existing records over to the new table and then delete the old one ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by M Owen
    Ted,

    An autonumber does not need be a primary key ... As for re-sequencing, the only way to do that is to copy the structure to another table name and then copy the existing records over to the new table and then delete the old one ...
    Ever heard it used as anything else?

    I suppose you could be right though.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Ever heard it used as anything else?

    I suppose you could be right though.
    Uh ... Ted. I do. I use it for a record count in continuous forms ... I stay away from using autonumbers as PKs because I have to control the assignment of IDs since the ID is not assigned until the info is saved ... Advantage? NO GAPS. (Cancel that record and come back in again and bingo- instant gap with an autonumber ...)
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    I had to change that situation up some too so looked into creating AutoIncrementing numbers that are not primary keys. Works for me but took a while to learn what I was given.
    Check out this link....

    http://www.candace-tripp.com/_pages/..._downloads.asp

    have a nice one,
    Bud

Posting Permissions

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