Changing a number field to Autonumber

[Pitlochry1]

What is the easiest way of amending a Field in a table that has thousands of
entries in it to be Autonumber?

I have managed to copy structure and amend an empty table to have autonumber
but cant figure out how to copy the records from original table into the copy
with amended field?

And finally would a renaming of new table to old table name knock out all
queries and reports?

Any advice would be appreciated
Glenn

 

[Keith Wilby]

What is the easiest way of amending a Field in a table that has thousands
of
entries in it to be Autonumber?

I have managed to copy structure and amend an empty table to have
autonumber
but cant figure out how to copy the records from original table into the
copy
with amended field?

And finally would a renaming of new table to old table name knock out all
queries and reports?

Make an empty copy of the table. Create the autonumber field in it. Use an
append query to copy the records from the old table. The a/n field will
populate automatically.

Your queries should be OK provided that you don't open them whilst a source
table is not present.

Keith.
www.keithwilby.co.uk

 

[Jack Cannon]

You state that you are changing a number field to autonumber.

This sounds very dangerous. If you are using the original number field as a
foreign key in another table then the action you are contemplating could
destroy the validity of your database. It is very unlikely that the value of
your new autonumber will by the same as the old number. If that happens then
the subordinate records will connect to a different primary record. (i.e.
Customer A's purchases will be reassigned to Customer B.)

It is okay to change the field only if the number is not being used
elsewhere. If it is being used elsewhere then it is still possible to
accomplish the objective but the methodology is far more complex than
creating a new table and appending the original records. Just be careful!

Jack Cannon

 

[John W. Vinson]

This sounds very dangerous. If you are using the original number field as a
foreign key in another table then the action you are contemplating could
destroy the validity of your database. It is very unlikely that the value of
your new autonumber will by the same as the old number. If that happens then
the subordinate records will connect to a different primary record. (i.e.
Customer A's purchases will be reassigned to Customer B.)

Actually an Append query will append a Long Integer field into a newly created
autonumber field, preserving the value. The autonumber seed will end up being
one more than the largest existing value.

It's not something I'd recommend doing often, but it can in fact be done
safely.

 

[Jack Cannon]

Thanks John,

I never knew that would work.

Jack Cannon

 

[John W. Vinson]

On Wed, 25 Feb 2009 09:55:01 -0800, Jack Cannon


It's pretty obscure and I remember learning it here, being surprised that it
COULD work, and finding it pretty useful for fixing messed-up databases!
So I thought it worthwhile to pass it on.