How to query and compare remote, not linked, databases in Access-CodePudding

I've got 2 remote access databases which I can query from another access database in the following way:

SELECT * FROM   (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\A.accdb')
SELECT * FROM   (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\A.accdb')

TABLE A

ID	DESCRIPTION
1	Jesse Pinkman
2	Skyler White
3	Henk Schrader

TABLE B

ID	DESCRIPTION
1	Jesse Pinkman
2	Skyler White
3	Henk Schrader
4	Saul Goodman

I am trying to compare both tables in the following way, but it doesn't return anything

SELECT *
FROM 
    (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\A.accdb') x 
LEFT JOIN 
    (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\B.accdb') y 
ON (x.[ID] = y.[ID]) 
WHERE ((y.ID) Is Null)

My question is, how do I compare two remote access databases.
The desired outcome of above example would be:

ID	DESCRIPTION
4	Saul Goodman

CodePudding user response：

If you want the difference in both files you have do this:

SELECT 'New in A file', X.*
FROM 
    (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\A.accdb') x 
LEFT JOIN 
    (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\B.accdb') y 
ON (x.[ID] = y.[ID]) 
WHERE ((y.ID) Is Null)

UNION ALL

SELECT 'New in B file', X.*
FROM 
    (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\B.accdb') x 
LEFT JOIN 
    (SELECT * FROM DGConcept IN 'C:\Users\Gusto\Desktop\A.accdb') y 
ON (x.[ID] = y.[ID]) 
WHERE ((y.ID) Is Null)

CodePudding user response：

Likely you'll need to create a link to the other table using VBA and the ADODB library if you're using Access.

MS Access Link Table With VBA