Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    25

    Unanswered: Import records to have same value as the source "AutoNumber" value

    I have a Access table for customers, where the customer number is set as "AutoNumber" and the table has about 200 records.

    I have a similar table with some additional fields, and want to import all the 200 records from the original table, when I did this I am not getting the same "AutoNumber" filed values in the new table.

    How can I import records where I wan to have the "AutoNumber" values to be identical to my original table?

    Thank you

  2. #2
    Join Date
    Jan 2005
    Posts
    362
    A way is to export the values as txt (values separated with ',') and then import them at the other db addig other field too with null or something) values

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Uh, how would that force values into an autonumber column dimis?

    As far as I know, you can't manually define the value of an autonumber column, because it's... well... "auto".

    You'd be better off appending your new fields to the original table.
    oh yeah... documentation... I have heard of that.

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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Teddy
    Uh, how would that force values into an autonumber column dimis?

    As far as I know, you can't manually define the value of an autonumber column, because it's... well... "auto".

    You'd be better off appending your new fields to the original table.
    agree
    and the best (probably only way to do that is going to be a manual VBA procedure / function

    another appraoch, if the numbers of rows are low is to create a new column in the source (the stuff you want to import), calling it say "NewCustNo" and set that value to be the same as the autonumber values for matching rows.
    then do a query which updates the relevant values in the destination table with the required values using the newly created column NewCustNo as you link / where clause.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    saddly, it is necessary to do this from time to time. You can't "force" autonumber to be your number, but you can cheat.

    1) Export the data to Excel, and make sure the data is sorted by the "autonumber".

    2) FIX the automunber column. Most often, this means adding rows (blank records) for any numbers that are missing. I generally use an IF statement in an adjacent column to make sure "this line equals the last line plus one" to quickly catch where the numbers are missing.

    3) Back in Access, you need to either make a new table or "reprime" the autonumber. Re-priming Autonumber is tricky at best, so you're best making a new table.

    4) Back in Excel, once your "autonumber" field is solid (continuous, in order, no gaps) you copy all the data and then PASTE APPEND in your new table in Access.

    5) Finally, delete any rows where there is a blank record. A delete query works good for this.

    Now, you may think you just reset the autonumber, but here's the flaw in that thought: when you paste into a table with Autonumber, Access will actually ignore the values in the autonumber field. But, if you correctly "fixed" the data in Excel so that you numbered from 1 to n without gaps, then it will match what Access does when you paste.

    ... and try not to make a habit of this, or you'll have to do some messy code to try and automate it.

    have fun,
    tc

Posting Permissions

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