convert a number to an autonumber

[Adam Robinson]

Hello,

I had an old Access database that had a bad design. I exported the data to
a csv, cleaned it up, and imported it into a new database. During the
import Access wouldn't let me change the field I use as a primary key which
was an autonumber type field to autonumber. Now it is a number datatype and
I need to change it to a autonumber but access won't let me. How can I
force access to make the change?

Thanks,

 

[Piet Linden]

Hello,

I had an old Access database that had a bad design.  I exported the data to
a csv, cleaned it up, and imported it into a new database.  During the
import Access wouldn't let me change the field I use as a primary key which
was an autonumber type field to autonumber.  Now it is a number datatype and
I need to change it to a autonumber but access won't let me.  How can I
force access to make the change?

Thanks,

Leave the data as a number when you import, then change it to an
autonumber (you might need to delete the relationships to other tables
that are based on that field first). Then compact the database and
try it again.

 

[Douglas J. Steele]

AFAIK, you cannot change a field to AutoNumber once the table is populated.

Set the field as AutoNumber before you import the data. The next value
assigned to the AutoNumber field should be one more than the largest number
you imported.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello,

I had an old Access database that had a bad design. I exported the data to
a csv, cleaned it up, and imported it into a new database. During the
import Access wouldn't let me change the field I use as a primary key
which
was an autonumber type field to autonumber. Now it is a number datatype
and
I need to change it to a autonumber but access won't let me. How can I
force access to make the change?

Thanks,

Leave the data as a number when you import, then change it to an
autonumber (you might need to delete the relationships to other tables
that are based on that field first). Then compact the database and
try it again.

 

[John Spencer MVP]

As far as I know, you cannot convert a field to autonumber once it has had a
value assigned to it.

I believe the way to do this is to design an EMPTY table with the correct
field types (including the autonumber field) and then import the data into the
table.

If that fails you may have to do a two-step process. Import the data from the
csv file into a table and then use and append query to copy the data into the
empty table that you have set up.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Adam Robinson]

Thank you. I ended up designing an empty table with the correct types and
imported the cvs into that.

Thanks,