Ms Access Join tables in separate databases

Question

im trying to create a query that will left join two tables(the tables are in two different databases in my local drive). So far I have come up with this SQL statement but when i run the query it tells me there is an error in the syntax in the FROM clause.

SELECT Daily_Report.* INTO AUDIT_TABLE
FROM Daily_Report LEFT JOIN [YTD-Daily_Report] 
IN 'C:\QA_Daily_YTD_Report_Export.accdb'
ON Daily_Report.RecordName = [YTD-Daily_Report].RecordName
WHERE ((([YTD-Daily_Report].RecordName) Is Null));

The query outputs a new Table(AUDIT_TABLE) with the records that do not exist in the external table(YTD-Daily_Report) when compared to the local table(Daily_Report).

I have never query tables in separate databases so im pretty lost here, any help would be much appreciated.

Answer 1

Does the following work for you:

SELECT dr.* INTO AUDIT_TABLE
FROM Daily_Report AS dr
LEFT JOIN [;database=C:\QA_Daily_YTD_Report_Export.accdb].[YTD-Daily_Report] AS ytd
   ON dr.RecordName = ytd.RecordName
WHERE ytd.RecordName Is Null