How to combine data from two separate access databases 2007
I have two identical databases with the same structure, database a on machine a and database b on machine b.
The data of database a **** (a.accdb) **** and database b **** (b.accdb) **** are different. then in database I have for example ID: 1, 2, 3 and in database B I Have ID: 4,5,6
Then I need to combine the data of these databases in only one database (a or b, it doesn't matter) so that the resulting database looks like. ID: 1,2,3,4,5,6
I am looking for an easy way to do this. because I have a lot of tables. and doing it with a union query is so tedious.
I'm looking for an example for a data-only backup option without a schema like in postgreSQl or many other RDBMSs, but I don't see those options in access 2007.
pd: only one table can have duplicate values (I think pk does not allow duplicate value to be copied and all other values will be copied). if i am wrong please correct me.
thanks for your help.
a source to share
If you have a tblFoo table with the same structure in both databases, you can try adding the contents of one table to the other. So, when opening a.accdb:
INSERT INTO tblFoo
SELECT *
FROM tblFoo IN 'C:\YourPath\b.accdb';
In fact, I'm skeptical that the approach will work for you because you mentioned primary keys and duplicates. You might be lucky enough to add content from both copies of tblFoo to a third table, tblFooMaster, which has no primary keys or unique constraints. In this situation, you will need to figure out what you want to do with the duplicates in tblFooMaster.
I hope you have backups of both databases before trying any consolidation method.
Edit . If this method is satisfactory, you do not need to create a query for every table. Use VBA to create 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 you prefer to perform table transfers as a single operation, you can use a different VBA procedure for your table names in 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
a source to share