Copy data from one database to another from several tables

[ThomasK via AccessMonster.com]

Hi,
I looked at previous post on this but they were not completely clear to me. I
built a database that has been used by only one user. He made a copy of it so
another person could input data also. (If he would have told me that he
wanted to have someone else use it I could have split the database and put it
on the server so they could both put data into the same tables, but what can
you do.) Now he wants me to combine the databases so they can have all the
data together.

It's a very simple database. It has three tables, each with a autonumber
primary key, and linked one to many with each other. Table One is many side
to table Two, and table Two is many side to table Three. Table One's primary
key is table Two's foreign key and table Two's primary key is table Three's
foreign key.

I need to combine the data of both databases from all the linked tables. My
fear is that somehow the autonumber keys are going to make this diffuicult.
Are my fears justified? How do I do this?

After I combine all the data I'm going to split the database and put it on
the server so I don't run into this problem again.

Thanks for the help.

Tom

 

[ThomasK via AccessMonster.com]

/

Hi,
I looked at previous post on this but they were not completely clear to me. I
built a database that has been used by only one user. He made a copy of it so
another person could input data also. (If he would have told me that he
wanted to have someone else use it I could have split the database and put it
on the server so they could both put data into the same tables, but what can
you do.) Now he wants me to combine the databases so they can have all the
data together.

It's a very simple database. It has three tables, each with a autonumber
primary key, and linked one to many with each other. Table One is many side
to table Two, and table Two is many side to table Three. Table One's primary
key is table Two's foreign key and table Two's primary key is table Three's
foreign key.

I need to combine the data of both databases from all the linked tables. My
fear is that somehow the autonumber keys are going to make this diffuicult.
Are my fears justified? How do I do this?

After I combine all the data I'm going to split the database and put it on
the server so I don't run into this problem again.

Thanks for the help.

Tom

 

[John Spencer]

Yes it will be a bit difficult because of the autonumbers.

I would try the following.
BACKUP your two databases in case this goes wrong.

In one of the two databases, add a new long integer field to each table.
This long integer will become the new primary key when you merge the data

In each of these tables run an update query to set the value of the new
primary key. I 'm going to assume an arbitrary offset of 50,000. You will
need to investigate the other database to see what the max autonumber value
is and select a value larger than it.
UPDATE Table1
SET NewPK = CurrentPrimaryKey + 50000

Remove the relationships between the three tables and using the same offset,
update the the existing foreign keys in the tables
UPDATE Table2
SET ForeignKey = ForeignKey + 50000

NOW, you can link the tables from this modified database to the other
database and run append queries. When you do so, use the NewPK fields to
set the value of the Primary keys

INSERT INTO DatabaseA.Table1 (PrimaryKey, ForeignKey,
SomeField,SomeOtherField)
SELECT NewPK, ForeignKey, SomeField, SomeOtherField
FROM DatabaseB.Table1

Repeat for the other two tables.
TEST TEST TEST.

 

[ThomasK via AccessMonster.com]

Thanks,

Tom

Yes it will be a bit difficult because of the autonumbers.

I would try the following.
BACKUP your two databases in case this goes wrong.

In one of the two databases, add a new long integer field to each table.
This long integer will become the new primary key when you merge the data

In each of these tables run an update query to set the value of the new
primary key. I 'm going to assume an arbitrary offset of 50,000. You will
need to investigate the other database to see what the max autonumber value
is and select a value larger than it.
UPDATE Table1
SET NewPK = CurrentPrimaryKey + 50000

Remove the relationships between the three tables and using the same offset,
update the the existing foreign keys in the tables
UPDATE Table2
SET ForeignKey = ForeignKey + 50000

NOW, you can link the tables from this modified database to the other
database and run append queries. When you do so, use the NewPK fields to
set the value of the Primary keys

INSERT INTO DatabaseA.Table1 (PrimaryKey, ForeignKey,
SomeField,SomeOtherField)
SELECT NewPK, ForeignKey, SomeField, SomeOtherField
FROM DatabaseB.Table1

Repeat for the other two tables.
TEST TEST TEST.

Hi,
I looked at previous post on this but they were not completely clear to

[quoted text clipped - 30 lines]

Tom