Autonumber quandary

[mscertified]

I have a table with an autonumber key which has records with autonumber 1-75
I need to import to this table some older records which must have earlier
autonumbers.
I figured I could copy the existing records to a new table with an
autonumber starting at say 1000, then change the start number to 1 and import
the old records.
Would something like this work?

 

[John Vinson]

I have a table with an autonumber key which has records with autonumber 1-75
I need to import to this table some older records which must have earlier
autonumbers.
I figured I could copy the existing records to a new table with an
autonumber starting at say 1000, then change the start number to 1 and import
the old records.
Would something like this work?

Not easily.

The AUtonumber value should not have any meaning other than that it is
unique. I'd suggest *linking* to the other table with older records,
and create an append query, appending all the fields EXCEPT the
autonumber. New autonumbers (starting with 76 in your example) will be
assigned to the imported records, regardless of their previous
autonumber values.

Of course this won't work if there are related tables with the
autonumber values in foreign key fields - post back if that's an
issue.

John W. Vinson[MVP]

 

[KARL DEWEY]

if there are related tables with the autonumber values in foreign key fields
What I would do is temporarily add a new field to the primary table to
capture the old primary key as the records are appended. Then you can join
the related table on the old primary key and update with the new key.

 

[John Vinson]

What I would do is temporarily add a new field to the primary table to
capture the old primary key as the records are appended. Then you can join
the related table on the old primary key and update with the new key.

Good idea, Karl! Thanks!

John W. Vinson[MVP]

 

[david epsom dot com dot au]

If you have linked tables, be careful about appending to an Autonumber
field. In the current version there is a bug, it re-sets the autonumber
key.

So if you have 1000-1075, and you append 1-998, the next record will
be 999, and the next record will be an error, duplicate key.

(david)

 

[Steve Albert]

Karl,

I have that exact issue. I have 2 DBs. They are the exact same database,
except one has old data that I need to transfer to the "new" database. The
autonumber key in the tables of both databases are related to other tables,
so they cannot change. Unfortunately, both old and new database tables have
the same autonumbers. Can you be more specific about how to append the data
from the old database table to the new database table and maintain the
relationships?

Thanks.

- Steve

 

[John Vinson]

Karl,

I have that exact issue. I have 2 DBs. They are the exact same database,
except one has old data that I need to transfer to the "new" database. The
autonumber key in the tables of both databases are related to other tables,
so they cannot change. Unfortunately, both old and new database tables have
the same autonumbers. Can you be more specific about how to append the data
from the old database table to the new database table and maintain the
relationships?

If you have the same value of the Primary Key in the two tables, what
do you want to happen? Do you want to add the record? You can't (not
as designed), since that would cause a duplicate primary key value; or
do you want to update the existing record with the data from the other
table?

John W. Vinson[MVP]

 

[Steve Albert]

John,

Thanks for the reply.

I would like to have the "old" data incorporated into the new table. I
really don't care about the Autonumber, I just need the related data to stay
related in the new table. This is the structure:

DB1:
TableA and TableB are related in a one to many relationship.

DB2:
Same exact tables but with different data. Unfortunaltely, the Autonumbers
are the same in both TableA's. I just want the data from the two tables in
DB1 to be appended to the two tables in DB2 with the data intact and still
related to each other.

 

[John Vinson]

DB1:
TableA and TableB are related in a one to many relationship.

DB2:
Same exact tables but with different data. Unfortunaltely, the Autonumbers
are the same in both TableA's. I just want the data from the two tables in
DB1 to be appended to the two tables in DB2 with the data intact and still
related to each other.

So the item in DB2 with Autonumber value 318 has nothing whatsoever to
do with the value in DB1 with 318, and you want to import it (and all
its related records) into the next available unused autonumber? OUCH.

About the best way I can think of requires several steps. Add a Long
Intger field to the "main" table (I'll call it Main) in DB1. This is a
placeholder for the "old" autonumber value. Link to DB2, and run an
Append query from the DB2 instance of Main into the new Main table,
appending the autonumber value to this new field; let Access assign
its autonumber key.

Then create an Append query into the DB1 Child table, linking the new
Main table to the DB2 Child table, linking from the placeholder field
to the foreign key field. Append the newly assigned autonumber value
to the child table's foreign key field, and all of the other DB2 child
table fields to the corresponding DB1 child table fields.

Then delete the placeholder field.

Needless to say - BACK UP EVERYTHING first and TEST EVERYTHING after!

John W. Vinson[MVP]