Re: merge multiple databases

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

  • Next message: Jim Carlock: "Re: merge multiple databases" 
    Date: Mon, 10 May 2004 13:25:13 -0500

    "DIOS" <sindizzy.pak@softhome.net> wrote in message
    news:UsDnc.17151$iF6.1791567@attbi_s02...
    > I have multiple Access97 databases that i want to merge into one single big
    > database. I have some ideas on how to do it but would like any suggestions
    > on doing the mrege more efficiently. Currently i am using a VB app to
    > connect to
    > the various databases via DAO. The tables look like so:

    <snip>

    After reading this thread, let me throw this out at you. I apologize in advance
    for word wrap:

        sSQL = "INSERT INTO tblOne "
        sSQL = sSQL & "IN '" & connLocal.Properties("Data Source").value & "' "
        sSQL = sSQL & "SELECT tblOne.* "
        sSQL = sSQL & "FROM tblOne "
        sSQL = sSQL & "IN '" & connServer.Properties("Data Source").value & "' ; "
        connLocal.Execute sSQL

    Let me explain that a bit. connLocal is a connection to one Access database.
    connServer is a connection to another Access database. The Data Source property
    of the connection objects is the path to the actual mdb file. You would be
    executing an insert into a table in a specific database all the record from a
    select statement on another database.

    Now, this may or may not work for you because of your autonumber situations.
    Well, if you have autonumber and this may mess everything up, then add in some
    columns into the new database to hold the oldDriveID (bad name). Then, after
    you have all your records in your new database, you can issue some cleanup
    routines to update the ids of the child tables to the new ids, based on the
    oldDriveId field.

    Does that help?

    Matt

  • Next message: Jim Carlock: "Re: merge multiple databases"

    Relevant Pages

    • Re: ADO Connection Timeout
      ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
      (microsoft.public.data.ado)
    • Re: ADO Connection Timeout
      ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
      (microsoft.public.data.ado)
    • Re: ADO Connection Timeout
      ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
      (microsoft.public.data.ado)
    • Re: ADO Connection Timeout
      ... much rather write stored procedures and use server side cursors. ... local database, it is vital that I get the information to a central server. ... Once the connection is restored, ...
      (microsoft.public.data.ado)
    • Re: ESQL/C documentation nightmare
      ... documentation about explicit and implicit connections. ... CONNECTION connection statements for new applications of Version 6.0 ... the SQL database statements (such as ...
      (comp.databases.informix)