Keeping Autonumber on re imported file

  • Thread starter Thread starter Chris Tanti
  • Start date Start date
C

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?
 
Chris said:
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?
Click to expand...

Your best bet is to create a real Contact_ID and not use autonumber for
it. Autonumbers do only one thing. They create a unique number for each
record. This should be used for linking tables and nothing more. The user
should never see it, because they will just get confused when they are not
consecutive.

Since you know that compacting the database and then re-creating the
number usually creates the numbers you want, you know the usual tricks to
try and fix the problem caused by using autonumbers where they should not be
used.
 
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.
Click to expand...

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.
 
Back
Top