How to merge data from two separate access 2007 databases

Question

I have two identical databases with same structure, database a in computer a and database b in computer b.

The data of database a****(a.accdb)**** and database b****(b.accdb)**** are different. then in database a i have for example ID:1, 2, 3 and in database B i Have ID:4,5,6

Then i need merge these databases data in only one database(a or b, doesn't matter) so the final database looks like. ID:1,2,3,4,5,6

I search an easy way to do this. because i have many tables. and do this by union query is so tedious.

I search for example for a backup option for only data without scheme as in postgreSQl or many others RDBMS, but i don't see this options in access 2007.

pd:only just table could be have duplicate values(I guess that pk doesn't allow copy a duplicate value and all others values will be copied well). if i wrong please correct me.

thanks for your help.

Answer 1

If you have a table, tblFoo, with the same structure in both databases you could try appending the contents of one table into the other. So with a.accdb open:

INSERT INTO tblFoo
SELECT *
FROM tblFoo IN 'C:\YourPath\b.accdb';

Actually I'm skeptical that approach will work for you because you mentioned primary keys and duplicates. Perhaps you would have better luck appending the content from both copies of tblFoo into a third table, tblFooMaster, which doesn't have any primary key or unique constraints. In that situation you would have to figure out what you want to do with the duplicates in tblFooMaster.

I hope you have backups of both databases before trying any form of consolidation.

Edit: If that method is satisfactory, you don't need to create a query for each table. Use VBA to build and execute the query for you.

Public Sub ImportTableData(ByVal pstrTable As String, ByVal pstrDb As String)
    Dim strSql As String
    strSql = "INSERT INTO [" & pstrTable & "]" & vbNewLine & _
        "SELECT *" & vbNewLine & _
        "FROM [" & pstrTable & "] IN '" & pstrDb & "';"
    'caller will handle errors
    CurrentDb.Execute strSql, dbFailOnError
End Sub

Since your preference is to do the table transfers as a single operation, you can have another VBA procedure feed your table names to the ImportTableData procedure.

Public Sub ImportAllTables()
    Const cstrDb As String = "C:\YourPath\b.accdb"
    Dim tdf As TableDef
    Dim strMsg As String

On Error GoTo ErrorHandler

    For Each tdf In CurrentDb.TableDefs
        'ignore system and temporary tables
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            ImportTableData tdf.Name, cstrDb
        End If
    Next tdf

ExitHere:
    On Error GoTo 0
    Set tdf = Nothing
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 3078
        strMsg = "Input table " & tdf.Name & " not found."
        MsgBox strMsg
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure ImportAllTables"
        MsgBox strMsg
        GoTo ExitHere
    End Select
End Sub