Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: Merging Two Databases with over 200 tables

    Hi, there, I'm having a really hard time with this and been searching the internet for some time for a solution, but my problem is kind of unique, so I hope you can help!

    I have two databases needing to be merged into one. Sounds simple, but there are over 200 tables that have the same structure and name but unique data in those tables.

    Obviously, I've gotten as far as importing the tables into one of the databases, but the tables that are imported in are automatically renamed with a "1" at the end of the table. (EX: Table1)

    I've read suggestions about doing a UNION Query and have tried the following:
    SELECT *
    FROM Table
    UNION SELECT *
    FROM Table1;

    This UNION query does "work" per se, but it doesn't create a new table to replace the old ones, and it doesn't keep the relationships that the old tables have.

    From that UNION query, I tried exporting it into Excel and re-Importing it back in Access as a Table, but like I said, relationships are not kept. And as you can imagine, creating relationships for over 200 tables took a long time...

    So what I need is a way to merge the two tables into one table while keeping the relationship to the master table in tact.

    Or any other ideas about merging these two databases would be helpful.

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In an Access database, the relationships among tables can be instanciated through the Relations collection of the Database object. The Database object has several collections of objects that represent the contents of the database:
    Code:
     ----------       -----------
    | Database | --> | TableDefs |
     ----------       -----------
                      ------------
                 --> | Containers |
                      ------------
                      -----------
                 --> | QueryDefs |
                      -----------
                      ------------
                 --> | RecordSets |
                      ------------
                      -----------
                 --> | Relations |
                      -----------
    You can instanciate the currently open database using:
    Code:
        Dim dbs As DAO.Database
    
        Set dbs = CurrentDb
    From there, you can enumerate the Relations collection:
    Code:
        Dim rel As DAO.Relation
    
        For Each rel In dbs.Relations
            Debug.Print rel.Name
        Next rel
    Each Relation object comprises a set of properties, some of them also being collections, such as the Fields collection which enumerates the Fields involved in a relationship among tables. Each Relation object represents a relationship between two tables. The Table property contains the name of the first table (left side of the relationship), while the ForeignTable property contains the name of the second table (right side of the relationship).

    As an example, lets have three tables: Tbl_Customers (primary key: Customer_Number), Tbl_Orders (primary key: Order_Number, foreign key: Customer_Number) and Tbl_OrderItems (primary key: Item_Number, foreign key: Order_Number). These tables are joined by relationships (see attached screenshot):
    Code:
    Tbl_Customers.Customer_Number  Tbl_Orders.Order_Number 
                          |                        |
                          |                        +--> Tbl_OrderItems.Order_Number
                          |
                          +------> Tbl_Orders.Customer_Number
    We can enumerate there relationships whith the tables involded, like this:
    Code:
    Sub EnumRelations()
    
        Dim dbs As DAO.Database
        Dim rel As DAO.Relation
        
        Set dbs = CurrentDb
        For Each rel In dbs.Relations
            Debug.Print rel.Name
            Debug.Print , rel.Table; " --> "; rel.ForeignTable
        Next rel
        
    End Sub
    which yields:
    Code:
    Tbl_CustomersTbl_Orders
                  Tbl_Customers --> Tbl_Orders
    Tbl_OrdersTbl_OrderItems
                  Tbl_Orders --> Tbl_OrderItems
    Each Relation object also has a Fields collection. Of course, the Fields collection is composed of Field objects (a box in a box in a box...).

    Each Field object has a Name property, which contains the name of the field in the first table, and a ForeignName property, which contains the name of the associated field in the second table.

    Now we have (collections are in Black, Objects are in Blue and properties are in Green):
    Code:
     ----------       -----------
    | Database | --> | Relations |
     ----------       -----------
                        |     ----------
                        +--> | Relation | (Name, Table, ForeignTable, ...)
                              ----------
                                |     -------- 
                                +--> | Fields |
                                      --------
                                        |     ------- 
                                        +--> | Field | (Name, ForeignName, ...)
                                              -------
    We can now fully enumerate the relationships in a database. Using the sample database described above, whe have:
    Code:
    Sub EnumRelations()
    
        Dim dbs As DAO.Database
        Dim rel As DAO.Relation
        Dim fld As DAO.Field
        
        Set dbs = CurrentDb
        For Each rel In dbs.Relations
            Debug.Print rel.Name
            Debug.Print , rel.Table; " --> "; rel.ForeignTable
            For Each fld In rel.Fields
                Debug.Print , , fld.Name; " -> "; fld.ForeignName
            Next fld
        Next rel
        
    End Sub
    which yields:

    Code:
    Tbl_CustomersTbl_Orders
                  Tbl_Customers --> Tbl_Orders
                                Customer_Number -> Customer_Number
    Tbl_OrdersTbl_OrderItems
                  Tbl_Orders --> Tbl_OrderItems
                                Order_Number -> Order_Number
    We can use the CreateRelation method of the Database object to create a relation between two tables. Here, I'll re-create the relationship between Tbl_Orders and Tbl_OrderItems:
    Code:
    Sub CreateRelation(TableName As String, ForeignTableName As String, _
                       FieldName As String, ForeignFieldName As String, _
                       FieldType As Long, RelationType As Long)
    
        Dim dbs As DAO.Database
        Dim rel As DAO.Relation
        Dim fld As DAO.Field
        Dim strName As String
        
        Set dbs = CurrentDb
        strName = TableName & "_" & ForeignTableName
        Set rel = dbs.CreateRelation(strName)
        With rel
            .Table = TableName
            .ForeignTable = ForeignTableName
            Set fld = .CreateField(FieldName, FieldType)
            fld.ForeignName = ForeignFieldName
            .Fields.Append fld
            .Attributes = RelationType
        End With
        With dbs
            .Relations.Append rel
            .Relations.Refresh
            .Close
        End With
        Set dbs = Nothing
        
    End Sub
    which I can call using:
    Code:
    CreateRelation "Tbl_Orders", "Tbl_OrderItems", "Order_Number", "Order_Number", dbLong, dbRelationDontEnforce
    Note: For a full description of the objects, their properties, methods and collections, search in Access Help in the Microsoft DAO 3.60 section, DAO Objects Reference sub-section.

    If you have a list of the tables and fields that are needed to re-create relationships into a database, you can easily call this procedure into a loop, passing the necessary parameters in each iteration.
    Attached Thumbnails Attached Thumbnails Relationships.jpg  
    Last edited by Sinndho; 09-21-12 at 07:37.
    Have a nice day!

  3. #3
    Join Date
    Sep 2012
    Posts
    2
    Hi, Sinndho!

    Thank you so much for your detailed response! This gives me a lot to think about! I will let you know if I have any more questions!


    Thanks again!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    May 2007
    Posts
    38
    do you mean you want to import the data from one database into corresponding tables in another.

    if so then you can run a series of queries that do this sort of thing

    insert * into existingtable from newtable

    The catches are though - that you may need to do the tables in a particular order to avoid breaking and RI constraints. - and also that you may have a problem with either autonumbers, and clashing existing keys. - so it is a project that needs some forethought.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •