connect to multiple ms access databases using odbc

Question

When creating applications in MSAccess (VBA) you can connect to multiple databases (mdb files) by simply creating links to them. Now I have rewritten the user interface in C/C++ and using ODBC to connect to the database. How can I connect to a second database (mdb file) and joining data from tables from one database to the other. For instance database 1 (file1.mdb) contains a table invoice and database 2 (file2.mdb) contains a table prices. How can I join invoice with prices?

Parfait Parfait · Accepted Answer · 2018-06-21T21:21:48

Assuming both databases reside on same network/server or machine, consider distributed queries where you qualify file names in brackets which is allowable with the Jet/ACE SQL engine.

SELECT p.*, i.*
FROM [C:\Path\To\File1.mdb].[Prices] p
INNER JOIN [C:\Path\To\File2.mdb].[Invoices] i
ON p.ID = i.PriceID

You can even connect to Excel workbooks, assuming data is in tabular format starting in A1 cell with column headers:

SELECT p.*, e.*
FROM [C:\Path\To\File1.mdb].[Prices] p
INNER JOIN [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\Workbook.xlsx].[SheetName$] AS e
ON p.ID = e.PriceID

And same with CSV files:

SELECT p.*, c.*
FROM [C:\Path\To\File1.mdb].[Prices] p
INNER JOIN [text;database=C:\Path\To\CSV\Folder].CSVFile.csv AS c;
ON p.ID = c.PriceID

connect to multiple ms access databases using odbc

1 Answers