Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2004
    Thanked 0 Times in 0 Posts

    Reset data type to autonumber (2k)

    Hello -

    I initially created some tables with their keys set to data type of autonumber. I had to reset the data type to number to load data for a conversion since I had to specify the key values for the data conversion. Now I need to reset the data type to autonumber again but Access won't let me and says I need to create a new field which it will automatically fill. I can't do that since it would screw up all the relationships I have between the keys in the tables.

    How do I get around this??


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Reset data type to autonumber (2k)

    I'm afraid that you have painted yourself into a corner. Once an AutoNumber field has been converted to an ordinary number field, you won't be able to change it back - ever. You'll have to jump through a series of hoops to use an AutoNumber again:
    - Create a new field in the "parent" table and set its type to AutoNumber.
    - Create a new number field (Long Integer) in each of the "child" tables.
    - For each of the "child" tables, create an update query based on this table and the "parent" table, joined on the existing link fields, to populate the new number field with the appropriate value from the new AutoNumber field from the parent.
    - Delete the old links, and create new links based on the new AutoNumber field.
    - If you like, delete the old link fields.
    Make sure to create backup copies before starting this, and preferably during the process.

Posting Permissions

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