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
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
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)
On Error GoTo 0
Set tdf = Nothing
Select Case Err.Number
strMsg = "Input table " & tdf.Name & " not found."
strMsg = "Error " & Err.Number & " (" & Err.Description _
& ") in procedure ImportAllTables"