Convert to Autonumber data type from numerical or text field

[Guest]

Hello:

I was converting tables from the old version of Microsoft Access. My
autonumber field is text type now and I would like to convert it back to
autonumber type. Is is possible to do it once you have a data? I would
appreciate your assistance.

 

[Guest]

Hi Anna

You can't change a field back to autonumber.

The simple solution would be to sort your table as you want it then add a
new field autonumber as use this as the primary field. Then delete the other
number. But DO THIS ON A BACK UP 1st just to make sure its what you want.

 

[Douglas J. Steele]

Import the data as is (i.e.: leave it as a text field), or just link to it.

Create a new table identical to the existing table, except make the field in
question an Autonumber field. (Note that you can copy the structure only of
a table without having to copy its data).

You can then create an Append query that takes the data from the imported
(or linked) table and writes it to the new table. Assuming that the text
field can be converted to numbers, the values from that field will be
inserted into the Autonumber field, and the next record to be inserted into
the new table will have an Autonumber value 1 more than the largest value
already used.

 

[Guest]

Hi Douglas:

This worked perfectly ok. I copied the structure, created a new autonumber
field in the new table and appended all the records and my previous
autonumber to new autonumber field.

 

[Guest]

I tried the method proposed by Douglas (one down) and it worked ok (append
structure, create a new field in a new table and then append data). Your
method would not allow me to copy the existing data into autonumber field
within the same table , I believe.

 

[Guest]

Hi Anna

I am pleased you got your table sorted. Just a point - an autonumber really
should simply by used as a unique identfier and not for anything else. If
you need to convert the text field to an autonumber it seem to me (although I
can't see your app so I may be wrong) that your are using it for something
else. There are methods of creating sequential numbers that are not "auto"
if you really need one . There are many reasons for needing a sequential
number - but you should not use an autonumber for this.

Good luck with your project.

 

[Guest]

Hello:

I realize that there are other methods for generating seqential number.
However, autounmber type works fine for the application that I have.
Originally the tables had autonumber type field in a very old version of
Access - 2.0. I was retrieving some archived data and there was a reason
that this field had to be converted to text at the time. Thanks for your
advise.