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