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

The information of database a*(a.accdb)* and database b*(b.accdb)* will vary. then in database a i've for instance ID:1, 2, 3 as well as in database B i've ID:4,5,6

I quickly need merge these databases data in just one database(a or b, does not matter) therefore the final database appears like. ID:1,2,3,4,5,6

I search a good way to get this done. because i've many tables. and do that by union totally so tiresome.

I search for instance for any backup choice for only data without plan as with postgreSQl or many more RDBMS, however i aren't seeing this options in access 2007.

pd:only table might be have duplicate values(I suppose that pk does not allow copy a replica value and all sorts of others values is going to be replicated well). basically wrong please correct me.

interesting help.

For those who have a table, tblFoo, with similar structure both in databases you could attempt appending the items in one table in to the other. So having a.accdb open:

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

Really I am skeptical that approach is useful for you since you pointed out primary secrets and replicates. Possibly you'd have better luck appending this content from both copies of tblFoo right into a third table, tblFooMaster, which does not have primary key or unique constraints. For the reason that situation you would need to evaluate which you would like related to the replicates in tblFooMaster.

I think you'll have backup copies of both databases prior to trying any kind of consolidation.

Edit: In the event that technique is acceptable, you don't have to produce a query for every table. Use VBA to construct and execute the query for you personally.

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

As your preference would be to perform the table transfers like a single operation, you could have another VBA procedure feed your table names towards 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
            Call 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