Access ODBC: JOIN between tables in different database files

Question

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.

Things I have tried are for example creating a SQL statement like SELECT * FROM tbl_a1 INNER JOIN [file_name].tbl_b1 ON [file_name].tbl_b1.pk=tbl_a1.fk WHERE 1 where "file_name" is not supported.

So I've created a linked table using Microsoft Access, which works fine with SELECT * FROM tbl_a1 INNER JOIN tbl_b1 ON tbl_b1.pk=tbl_a1.fk WHERE 1.

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 documentation how to create linked tables using only Microsoft Access, but it does not tell me how to do it programatically.

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.

Solution: In my case I had to set an table alias for the second table SELECT * FROM tbl_a1 INNER JOIN [C:\path\db.mdb].tbl_b1 AS tbl_b1 ON tbl_b1.pk = tbl_a1.fk WHERE 1 to make it work. Thanks to @Gord Thompson

Answer 1

The approach using [mdbFileSpec].[tableName] should work if you are using Microsoft's own Access ODBC driver. I can't test this in your C++ environment, but I can confirm that using Python and pyodbc this does not work ...

# -*- coding: utf-8 -*-
import pyodbc

connStr = (
    r"Driver={Microsoft Access Driver (*.mdb)};"
    r"DBQ=C:\Users\Public\test\a1.mdb;"
    )
cnxn = pyodbc.connect(connStr)
sql = """\
SELECT *
FROM tbl-a1
INNER JOIN
[C:\\Users\\Public\\test\\b1.mdb].tbl-b1
    ON [C:\\Users\\Public\\test\\b1.mdb].tbl-b1.pk=tbl-a1.fk
WHERE 1
"""
crsr = cnxn.execute(sql)
for row in crsr:
    print(row)
crsr.close()
cnxn.close()

... but this works fine:

# -*- coding: utf-8 -*-
import pyodbc

connStr = (
    r"Driver={Microsoft Access Driver (*.mdb)};"
    r"DBQ=C:\Users\Public\test\a1.mdb;"
    )
cnxn = pyodbc.connect(connStr)    
sql = """\
SELECT *
FROM [tbl-a1] AS tbl_a1
INNER JOIN
[C:\\Users\\Public\\test\\b1.mdb].[tbl-b1] AS tbl_b1
    ON tbl_b1.pk=tbl_a1.fk
WHERE 1
"""
crsr = cnxn.execute(sql)
for row in crsr:
    print(row)
crsr.close()
cnxn.close()

Note that

1. I only used the file spec once and created table aliases to refer to the tables, and
2. I put square brackets around the table names because they contained hyphens that could be misinterpreted as minus signs.