Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Post Unanswered: reindex primary key after append query

    Hello,

    I performed an append action query on a table to have the records from one table append to a new table. The new table was empty. The primary key (ID) was auto-increment. There were 563 records appended to a empty table.

    After the append query, the first record has an ID of 564. For some reason auto-increment started at 564.

    Is there another query of tool within ACCESS to reindex the primary key ?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Does the table you appended from have an auto-increment field? If so, then when you do an append you have to drop the auto-increment column ... Access fills that column when doing any adds.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    On your reindex question: NO. You have to delete, recreate, and repopulate to resequence this kind of table ...

  4. #4
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    MOwen and 24jedi are right.... you'd have to drop the original index and repopulate the table to re-index it. This is because in most cases people want to keep the original index numbers intact.... it's a feature that savaed a lot of trouble here a few times...

  5. #5
    Join Date
    Nov 2003
    Posts
    10
    Thanks.

    The original table (source) that had the 563 records did not have an auto-increment field.

    The table I was appending to (destination) DID have an auto-increment field, which was also the PK. This table was also empty.

    What confused me was that after I imported the records, the auto-increment started at 564, one more that the totla number of records being imported.

    As a test, I created a copy (structure only) of the destination table and appended to it. The auto-increment started at 1, just as expected.

    I understand the significance of maintaining the orignal index, but in this case I was appending to a blank table.

    Anyway, I deleted the destination table and renamed the copied test table. Everything still works. Thanks for the insight.

    Don

Posting Permissions

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