Nancy Lytle
lytlenj at yahoo.com
Fri Jul 16 10:38:16 CDT 2004
Thank you, I was afraid this was what I was going to have to so since the tables do have parent child relationships down the line. Because they are taking material that we have ordered and that is received and they have to be able to track it all the way as it is assembled with other parts, until it is finished and placed in as part of a larger project, say a piece to go on an airplane. So I guess really they want to be able to also "backtrack" if necessary from the finished part to the Lot it came in. Again, thanks for the assistance. Now I need to map all this out. Nancy --- "Colby, John" <JColby at dispec.com> wrote: > Nancy, > > You are lucky it is just 10 tables. It is possible > to do this by: > > 1) Create a NEW database for the migration > 2) Link all of the tables from DB1 > 3) link all of the tables from DB2 > 4) Create queries that append one table in DB1 to > it's matching table in DB2 > 5) Start by creating SELECT queries (so you can see > the data). In these > queries, take the PK of the table in DB1 and alias > it, adding a large number > to it (at least the quantity of records in DB2). > IOW if DB2 has 2000 > records in the matching table then the aliased PK in > db1 should look like: > PK: [RealPKFldName]+2000 > 6) Once you are comfortable you have this right, > turn the SELECT into an > append query and append the data into the table > > What you have done is simply "bumped" the PK up > enough that they will append > in to DB2 without colliding with any existing PKs. > > > NOW... any tables that use the PK of that table as a > FK... > > You have to "Bump" the FK by that same number (2000 > in my example). > > You get the idea? > > Build a system of queries that do this in order from > parent to child table. > Build macros that sequence the queries in the > correct order. > Using TEST database copies of DB1 and DB2 run the > macros. > Test that the migration worked. > Fix problems. > Do it again until it works > > Once you know that everything is working... > > BACKUP the real DB1 and DB2 > Run the macro. > > You are done. > > Doing it this way allows you to do the migration in > just the time required > for the update / append queries to work. > > JWC > > -----Original Message----- > From: Nancy Lytle [mailto:lytlenj at yahoo.com] > Sent: Friday, July 16, 2004 10:45 AM > To: AccessD at databaseadvisors.com > Subject: [AccessD] Take a formatted number and turn > it into a "real" > number > > > I just got handed a database of about 30 tables > (about > half are lookups), there are about 10 tables with > varying relationships that are fairly well defined. > There is an identical - but unconnected - version of > the database at our West Coast location. > I have now been tasked with merging the two. At > first > I thought it would be easy because it appeared they > were using two different numbers schemes for the > PK's > turns out they just formatted them to "look > different" > but the matching tables use the same Autonumber - > start from 1 - field type. So now I have to figure > out a way to merge them without messing up all the > relationships. > Does anyone have any ideas? Both databases are > still > being used so I have to come up with a way I can do > it > with the least down time possible. > TIA, > Nancy > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >