Merging 9 stand alone Databases

[cbrashear]

I have inherited 9 stand alone databases that need to be merged into one
package.
The problem is the auto number it is the primary key and unique Identifier
for aprox 20 tables in each database. Each database structure is identical so
when I merge the first set of data tables into the master I have no problems,
but after this I can not merge the additional databases sucsessfully. The
data comes in but does not look for its offsprings

Example

tblstudent
field studentID is a auto number

tblworkhistory

field studentID pulls auto number from tblstudent

Except all 9 database have auto number 1 in tblstudent. Now all nine attach
studentId to the first merged database in the master.

I believe I must go into each database and break all relationships. Then
change the auto number fields to number in all tables that have one to many
relationships. Next reestablish all relationships back to cascade update.
Next I would Renumber all old autonumber fields being sure the numbers are
higher than the preceding database. Lastly I would merge all tables into
master database which is still set as a auto number

This is the only way extemely time consuming. Am I on the right page or am I
making it to hard

 

[Jerry Whittle]

First make complete backups of all the databases just in case things go wrong.

Next make sure that any joins between the tables have Referential Integrity
enabled and Cascade Update selected. RI with Cascade Update should
automatically take care of updating any FK fields in child tables. That way
you won't need to break relationships.

Then change the autonumber PK fields to Numbers EXCEPT for in the master
table where you are going to pour in all the data. You can't change a number
field to an Autonumber PK once there is data in the table.

Now starting with the topmost parent table, change the primary key field by
adding an appropriately large number to it. In the case below, 100000 would
be the first table. Then use 200000 for the second table. And so on something
like below.

UPDATE tblstudent SET tblstudent.studentid = [studentid]+100000;

Work down to any child tables that also need their PK updated.

Then you should be able to import all the data from similar tables into one
Starting with the parent tables first

 

[cbrashear]

The problem is I can not change the auto number field to number with the
relationships intact
--
Cbrashear

First make complete backups of all the databases just in case things go wrong.

Next make sure that any joins between the tables have Referential Integrity
enabled and Cascade Update selected. RI with Cascade Update should
automatically take care of updating any FK fields in child tables. That way
you won't need to break relationships.

Then change the autonumber PK fields to Numbers EXCEPT for in the master
table where you are going to pour in all the data. You can't change a number
field to an Autonumber PK once there is data in the table.

Now starting with the topmost parent table, change the primary key field by
adding an appropriately large number to it. In the case below, 100000 would
be the first table. Then use 200000 for the second table. And so on something
like below.

UPDATE tblstudent SET tblstudent.studentid = [studentid]+100000;

Work down to any child tables that also need their PK updated.

Then you should be able to import all the data from similar tables into one
Starting with the parent tables first
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have inherited 9 stand alone databases that need to be merged into one
package.
The problem is the auto number it is the primary key and unique Identifier
for aprox 20 tables in each database. Each database structure is identical so
when I merge the first set of data tables into the master I have no problems,
but after this I can not merge the additional databases sucsessfully. The
data comes in but does not look for its offsprings

Example

tblstudent
field studentID is a auto number

tblworkhistory

field studentID pulls auto number from tblstudent

Except all 9 database have auto number 1 in tblstudent. Now all nine attach
studentId to the first merged database in the master.

I believe I must go into each database and break all relationships. Then
change the auto number fields to number in all tables that have one to many
relationships. Next reestablish all relationships back to cascade update.
Next I would Renumber all old autonumber fields being sure the numbers are
higher than the preceding database. Lastly I would merge all tables into
master database which is still set as a auto number

This is the only way extemely time consuming. Am I on the right page or am I
making it to hard

 

[Chris]

If you delete the relationship, change the autonumber field to number,
recreate the relationship, and then run the update query, you should be fine.

HTH,
Chris

The problem is I can not change the auto number field to number with the
relationships intact
--
Cbrashear

First make complete backups of all the databases just in case things go wrong.

Next make sure that any joins between the tables have Referential Integrity
enabled and Cascade Update selected. RI with Cascade Update should
automatically take care of updating any FK fields in child tables. That way
you won't need to break relationships.

Then change the autonumber PK fields to Numbers EXCEPT for in the master
table where you are going to pour in all the data. You can't change a number
field to an Autonumber PK once there is data in the table.

Now starting with the topmost parent table, change the primary key field by
adding an appropriately large number to it. In the case below, 100000 would
be the first table. Then use 200000 for the second table. And so on something
like below.

UPDATE tblstudent SET tblstudent.studentid = [studentid]+100000;

Work down to any child tables that also need their PK updated.

Then you should be able to import all the data from similar tables into one
Starting with the parent tables first
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have inherited 9 stand alone databases that need to be merged into one
package.
The problem is the auto number it is the primary key and unique Identifier
for aprox 20 tables in each database. Each database structure is identical so
when I merge the first set of data tables into the master I have no problems,
but after this I can not merge the additional databases sucsessfully. The
data comes in but does not look for its offsprings

Example

tblstudent
field studentID is a auto number

tblworkhistory

field studentID pulls auto number from tblstudent

Except all 9 database have auto number 1 in tblstudent. Now all nine attach
studentId to the first merged database in the master.

I believe I must go into each database and break all relationships. Then
change the auto number fields to number in all tables that have one to many
relationships. Next reestablish all relationships back to cascade update.
Next I would Renumber all old autonumber fields being sure the numbers are
higher than the preceding database. Lastly I would merge all tables into
master database which is still set as a auto number

This is the only way extemely time consuming. Am I on the right page or am I
making it to hard

 

[NetworkTrade]

The 9 may have coincidentally identical autonumber values. I believe you
need to overlay a new column/field in each table and insert a unique value
that when merged will assure no duplicates...

and then use the new field as the linking field to the relationship not the
autonumber field....

 

[David W. Fenton]

=?Utf-8?B?SmVycnkgV2hpdHRsZQ==?=

Then change the autonumber PK fields to Numbers EXCEPT for in the
master table where you are going to pour in all the data. You
can't change a number field to an Autonumber PK once there is data
in the table.

Now starting with the topmost parent table, change the primary key
field by adding an appropriately large number to it.

You're assuming incremented Autonumber fields. If they are random,
your approach could easily produce duplicates.

 

[Jerry Whittle]

Now that is embarressing as I alway harp that autonumbers need not be
sequential and could be almost any unique number!

Good point. Cbrashear would need to ensure that no number conflict.

 

[cbrashear]

That is correct when I re-number the old autonumber primary key I would
complete one database at a time when I moved to the next one I am insuring my
numbers are higher than the set I moved in before it. I would then use an
append query to move the data is moving from the base up so all parents
appended before child tables and fields. All of this works well the main
headache was breaking all those relationships and them reconnecting them. I
had hoped I was making it too complicated but we all seem to be on the same
page.

The master would be set as auto number so after all the data is in the
autonumber field would continue to provide a unique key automatically as we
move forward