Joining a query from a separate database

Question

I have two Access 2007 databases, DB1 and DB2 for convention, and am trying to outer join the results of a query (Q1) in DB1 with a query (Q2) in DB2.

My code looks like this

SELECT 
  Q2.a, 
  Q2.b, 
  Q2.c, 
  Q1.d

FROM
  [Full name of Q1] IN 'C:\Users\...\DB1.accdb' AS Q1
    RIGHT JOIN
      [Full name of Q2] AS Q2
    ON Q2.a = Q1.a 
;

But returns the error "Syntax error in FROM clause."

I have tried this method with LEFT JOIN, as well as trying far too many brackets around things in fruitless attempts to get this to work. What am I doing wrong?

Answer 1

That [source Name] IN 'file path' syntax is tricky. If you want to alias [source Name], you would need to include the alias between the [source Name] and 'file path' pieces (instead of after 'file path').

But even that only works for a query which includes a single data source. Once you join anything, the [source Name] IN 'file path' syntax breaks ... and is not fixable. You need a different approach ...

SELECT 
    Q2.a, 
    Q2.b, 
    Q2.c, 
    Q1.d
FROM
    [C:\Users\...\DB1.accdb].[Full name of Q1] AS Q1
    RIGHT JOIN [Full name of Q2] AS Q2
    ON Q2.a = Q1.a;