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

Question

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

Hogan · Accepted Answer · 2022-10-04T13:10:29.130

1

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)

edited Oct 04 '22 at 13:10

answered Oct 04 '22 at 12:46

Hogan

69,564
10
76
117

your solution gives a `syntax error in FROM clause` – MK01111000 Oct 04 '22 at 12:51
try it with the ( ) . You are doing this in MS Access? – Hogan Oct 04 '22 at 12:53
I am doing this in Access, Your update now gives a `syntax error in JOIN operation` (highlighting the `IN`) – MK01111000 Oct 04 '22 at 12:54
yes I looked at the docs -- looks like alias like this are not supported – Hogan Oct 04 '22 at 12:56
looks like your original code is correct -- what was the problem you were having with it? – Hogan Oct 04 '22 at 12:56
It does not return the differences between the 2 tables (results are empty, although there is a new record) – MK01111000 Oct 04 '22 at 12:58
is the new item in X or Y? – Hogan Oct 04 '22 at 13:08
In the example I described, the new item is in Table B = x – MK01111000 Oct 04 '22 at 13:14
I just saw your updated answer, This one works – MK01111000 Oct 04 '22 at 13:15

score 0 · Answer 2 · answered Oct 04 '22 at 12:57

0

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

answered Oct 04 '22 at 12:57

Glenn_co-co

16
1

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

2 Answers2