Retaining Autonumbers

[LarryP]

A dilemma. I'm merging three different databases into one, and in each
there's a "people" table with an autonumber ID field. I want to use
autonumber in the new merged database as well. If I could just bring across
all the names and let the system assign them a new number, no sweat, but for
ONE of the three databases, the user has put that number on about a jillion
forms and file folders, so they desperately want to keep the same number for
each person. (For additional persons brought in from the other two
databases, or entered in future, assigning a completely new number is fine
with them.)

So how, without some really arduous manual effort or complex VBA, can I
import the data from Database 1 with the EXISTING ID numbers? Over time I've
learned how to trick the Autonumber feature in a few ways, but this one has
me stumped. Any suggestions welcome.

 

[tina]

well, you can always import the data from Db1 to a temporary table, then
append it into the master table. you *can* APPEND values into an Autonumber
field; your only issue is to avoid duplicate values that would violate the
unique index (which has nothing to do with the data type being an
Autonumber, by the way - it's only an issue when a field is designated as
primary key, or a unique index is set on it). suggest you first import and
append the Db1 data. then import and append the other dbs' data - this time
not appending the Autonumber value to the master table; just let Access set
the Autonumber value for each appended record.

hth

 

[KARL DEWEY]

The best way I know is not easy but is simple to do. Review and record the
relations of the tables to be imported - people - related1, related2, etc.

Import the tables. Make the autonumbered field a number - integer. Add a
temporary field to each related table - number integer.

Append the main table so that the data appended has old and new autonumber
fields.

Run an update query on all of the related tables joined on the old
autonumber field, updating the temporary fields. Run a second query on the
related tables updating the old autonumber field from the temp field.
IMPORTED DATA ----
PEOPLE--
1 JOHN SMITH
CLASSES--
1 1 ALGEBRA

APPENDED ----
PEOPLE--
11 1 JOHN SMITH
CLASSES--
1 1 ALGEBRA [X]

UPDATED ----
CLASSES--
1 1 ALGEBRA [11]

2ND UPDATE ----
CLASSES--
1 11 ALGEBRA [11]