Results 1 to 2 of 2
2004-07-22, 17:17 #1
- Join Date
- May 2004
- Thanked 0 Times in 0 Posts
Reset data type to autonumber (2k)
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??
2004-07-22, 17:48 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 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.