Autonumber

[Peter Moore]

I have an Access 2000 table with a key field, that I would like to
change the data type from Number to Autonumber. Access says I am unable
to change the datatype to Autonumber. Is there a workaround?

Peter Moore

 

[Ken Snell]

If this key field is a foreign key in other tables, you will have a lot of
work to do in order to properly reestablish the links/relationships between
the tables, because all the foreign keys' values will need to be updated to
the new autonumber values for each record.

Essentially, you'd need to do the following steps:
1. Add new field to your table and make it an autonumber field.
2. Delete all existing relationships from current primary key to the other
tables.
3. Remove primary key designation from this current field. Establish the
autonumber field as the primary key field.
4. Run numerous update queries that will change the values of the foreign
keys to the corresponding autonumber field's value.
5. Establish new relationships between the autonumber field and the foreign
key fields in the other tables.

Good luck!

 

[Tim Ferguson]

1. Add new field to your table and make it an autonumber field.
2. Delete all existing relationships from current primary key to the
other tables.
3. Remove primary key designation from this current field. Establish
the autonumber field as the primary key field.
4. Run numerous update queries that will change the values of the
foreign keys to the corresponding autonumber field's value.
5. Establish new relationships between the autonumber field and the
foreign key fields in the other tables

There is also a way that avoids the messing about of step 4:

1. Create a new empty table with identical structure, except to change the
key column to an Autonumber, keeping the name the same. Don't forget to
recreate any Indexes and make the new key a PK.

2. Append the data from the old table to the new table. You can insert
values into an Autonumber column with an INSERT query, and the autonumber
seed will be reset beyond the highest one.

3. Remove all relationships from the old table, delete it, rename the new
table to what the old one was, and put back the relationships.

5. Test rigorously before dropping the backups -- you did remember step
zero, didn't you??


HTH

Tim F

 

[Ken Snell]

But if the values in the foreign keys' fields in the child tables don't
match the autonumber field (assuming that is the primary key), you'll still
need to update those child tables' foreign keys' values, right?

 

[Steve Schapel]

Ken,

If you use an Append Query, which I think Tim was implying, the values
of the new Autonumber field will be the same as the values in the old
Number field, so the issue you mention won't arise.

- Steve Schapel, Microsoft Access MVP

 

[Ken Snell]

< dope > !

I completely missed that sentence! And I read it three times!

back to football....American kind, that is!

Sorry, Tim!

 

[Tim Ferguson]

Sorry, Tim!

<g> All the best!


Tim F

 

[SUNBERRIES]

This thread helped me solve my problem when I created a new table and
lost the function of the autonumber from the original table.
However, in doin so, I also discovered an easier way:
Clear the primary key from the original and disconnect relationships.

1.At the Table Database view, right click and copy the original
database.
2. At a blank area on the same window, right click to paste the copied
table; a dialogue box will pop out prompting you to choose how the data
will be pasted; type the name of the new table then select " Structure
and Data" , then ok. This will create a new table exactly the same one
as the original, but this one will have no records in it, just the
fields and formats.
3. After closing, select the new table then go to design view,
from the design view edit the format of the field that needs to be
autonumbered, chang the "data type" into "autonumber" and make it the
primary key. then save.
4.Right click copy again the original table, then paste; again the
dialogue box will come out, this time, type the name of your new table
then select "append data to existing table" . Voila! its ba~~aacckk.
Then just rename the tables to switch.