RE: Merging 9 stand alone Databases
- From: Chris <Chris@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Feb 2008 08:53:02 -0800
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
"cbrashear" wrote:
The problem is I can not change the auto number field to number with the.
relationships intact
--
Cbrashear
"Jerry Whittle" wrote:
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.
"cbrashear" wrote:
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
--
Cbrashear
- References:
- Merging 9 stand alone Databases
- From: cbrashear
- RE: Merging 9 stand alone Databases
- From: Jerry Whittle
- RE: Merging 9 stand alone Databases
- From: cbrashear
- Merging 9 stand alone Databases
- Prev by Date: RE: Merging 9 stand alone Databases
- Next by Date: RE: Merging 9 stand alone Databases
- Previous by thread: RE: Merging 9 stand alone Databases
- Next by thread: RE: Merging 9 stand alone Databases
- Index(es):
Relevant Pages
|
