title: Access ODBC: JOIN between tables in different database files
body: <p>Let's say I have multiple tables distributed in different Access database files (*.mdb), which I want to combine with a "join" clause into a single dataset. The only solution right now I see is to create linked tables in a Access database, unless someone knows a better and cleaner solution.</p>
  
  <p>Things I have tried are for example creating a SQL statement like <code>SELECT * FROM tbl_a1 INNER JOIN [file_name].tbl_b1 ON [file_name].tbl_b1.pk=tbl_a1.fk WHERE 1</code> where "file_name" is not supported. </p>
  
  <p>So I've created a linked table using Microsoft Access, which works fine with <code>SELECT * FROM tbl_a1 INNER JOIN tbl_b1 ON tbl_b1.pk=tbl_a1.fk WHERE 1</code>. </p>
  
  <p>What I expect to see a solution to create it programatically, because "machine B" might not have Access installed, and the pre-defined database files do not include linked tables yet. Those tables do not have to be permanent either.
  Microsoft has a <a href="https://support.office.com/en-us/article/Import-or-link-to-data-in-another-Access-database-095ab408-89c7-45b3-aac2-58036e45fcf6#bm3" rel="nofollow">documentation</a> how to create linked tables using only Microsoft Access, but it does not tell me how to do it programatically.</p>
  
  <p>Note: I am using RAD 10 Seattle/VCL C++ Builder with UniDac for database connections by using the ODBC driver, so VBA is not an option.</p>
  
  <p>Solution: In my case I had to set an table alias for the second table <code>SELECT * FROM tbl_a1 INNER JOIN [C:\path\db.mdb].tbl_b1 AS tbl_b1 ON tbl_b1.pk = tbl_a1.fk WHERE 1</code> to make it work. Thanks to @Gord Thompson</p>