Keeping Autonumber on re imported file

[Chris Tanti]

I have had to address clean a table using software that
required the file in XL (or csv) format.

I exported the file from Access as an XL file and then
completed the cleaning, and I have reveresed the procedure
(importing the file back into access)

My problem? I cant reset the first field (Contact_ID) back
to Autonumber in Access! It refuses to change the field
type.

The auto number does not run perfectly sequentially (there
are gaps from deletions) and the file has been compacted.

How the heck do I get around this??

I have the original file (un cleaned) and the new file
(cleaned) both in access now.

Its important that I keep the original Contact_ID's.

Is this possible?

 

[John Vinson]

My problem? I cant reset the first field (Contact_ID) back
to Autonumber in Access! It refuses to change the field
type.

The auto number does not run perfectly sequentially (there
are gaps from deletions) and the file has been compacted.

How the heck do I get around this??

I have the original file (un cleaned) and the new file
(cleaned) both in access now.

Its important that I keep the original Contact_ID's.

I fully agree with Joseph that Autonumber is not appropriate for
human-visible Contact_ID's. If, however, you're using these ID's
behind the scenes as linking fields (which is appropriate), or you
want to use displayed autonumbers anyway, you can solve your problem
by creating a new Table. Copy and paste this table, *design mode
only*, to a new table; change the Contact_ID to Autonumber in the
empty table; run an Append query to transfer all the data from the old
table (appending the Long Integer ContactID into the new autonumber
field). Delete all the relationships to the old table and reestablish
them to the new one, rename the old table to Tablename_OLD and the new
table to the "real" table name; test everything (to make sure you're
not missing data and that new records are properly autonumbered), then
delete the old table and compact.