Re: merge multiple databases

From: YYZ (notapplicable)
Date: 05/11/04

  • Next message: DIOS: "Re: merge multiple databases" 
    Date: Tue, 11 May 2004 08:56:26 -0500

    "DIOS" <sindizzy.pak@softhome.net> wrote in message
    news:snYnc.24150$xw3.1632381@attbi_s04...
    > Hmmm, I can create queries in Access or on the fly but I dont think there
    > is a concise way of creating a query that will merge my databases into
    > one big database. Each table has a primary index which is tied in with the
    > other
    > tables. Each database can possibly have the same primary keys. So when
    > i merge the databases i have to keep track of what the new keys are and then
    > transfer those to the the other tables and then merge those into the new
    > database.
    > Sounds easy but I just dont see how to do it with a standard update query.

    I dont know if you saw my other post, but see if this would work for you:

    Add 2 columns to tblDrive -- dbId and oldDriveID.
    Add 2 columns to tblFiles -- dbId and oldFileID.
    Add 1 column to tblSubFiles -- dbId

    dbId would be text. You'd update every record in every table, set dbId to some
    unique identifier for each of your old databases. Would machine name work? How
    about Pathname? You could even use a numbering scheme (NOT autonumber! You are
    seeing why autonumber sucks sometimes, and GUIDs may work better for you in the
    long run).

    After you do that for each client datbase, then you can issue 3 sql statements
    per database, one per table, like I showed you in my other post INSERTing all
    records into the new datbase from teh old datbase, letting the new datbase
    assign all brand new autonumber values in DriveId (but you have oldDriveID,
    remember?).

    Now you ahve all the information you need, and I think you'll just need to issue
    3 update statements -- here are 2 of them...

    UPDATE tblSubFiles INNER JOIN tblFiles ON (tblSubFiles.FileID =
    tblFiles.oldFileId AND tblSubFiles.dbId = tblFiles.dbId) SET tblSubFiles.FileID
    = tblFiles.FileId

    UPDATE tblSubFiles INNER JOIN tblDrive ON (tblSubFiles.DriveId =
    tblDrive.oldDriveID AND tblSubFiles.dbId = tblDrive.dbId) SET
    tblSubFiles.DriveId = tblDrive.DriveID

    ' do the same thing for tblFiles

    Does that work for you?

    Matt

  • Next message: DIOS: "Re: merge multiple databases"

    Relevant Pages

    • Re: merge multiple databases
      ... Each database can possibly have the same primary keys. ... > Sounds easy but I just dont see how to do it with a standard update query. ... Add 2 columns to tblDrive -- dbId and oldDriveID. ... After you do that for each client datbase, then you can issue 3 sql statements ...
      (microsoft.public.access.queries)
    • Re: merge multiple databases
      ... Each database can possibly have the same primary keys. ... > Sounds easy but I just dont see how to do it with a standard update query. ... Add 2 columns to tblDrive -- dbId and oldDriveID. ... After you do that for each client datbase, then you can issue 3 sql statements ...
      (microsoft.public.vb.general.discussion)
    • Re: merge multiple databases
      ... Each database can possibly have the same primary keys. ... > Add 2 columns to tblDrive -- dbId and oldDriveID. ... > After you do that for each client datbase, then you can issue 3 sql statements> per database, one per table, like I showed you in my other post INSERTing all> records into the new datbase from teh old datbase, letting the new datbase ...
      (microsoft.public.vb.database.dao)
    • Re: merge multiple databases
      ... Each database can possibly have the same primary keys. ... > Add 2 columns to tblDrive -- dbId and oldDriveID. ... > After you do that for each client datbase, then you can issue 3 sql statements> per database, one per table, like I showed you in my other post INSERTing all> records into the new datbase from teh old datbase, letting the new datbase ...
      (microsoft.public.vb.general.discussion)
    • Re: merge multiple databases
      ... Each database can possibly have the same primary keys. ... > Add 2 columns to tblDrive -- dbId and oldDriveID. ... > After you do that for each client datbase, then you can issue 3 sql statements> per database, one per table, like I showed you in my other post INSERTing all> records into the new datbase from teh old datbase, letting the new datbase ...
      (microsoft.public.access.queries)
    Loading