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.
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