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?
1 Answer
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
4 Comments
Eugène Bindels
Where is this documented?
Parfait
These may be undocumented methods, possibly because it is advanced for most users of Access and Excel. However there is documentation of SQL Server connecting to Access databases and Excel workbooks via distributed queries. If you want an official route, use Access linked tables pointing to other Access sources, then connect to that one database to query all needed tables.
Eugène Bindels
The beauty of using odbc is that the end user doesn't have to have MSAccess installed. When using a linked table solution, how do you update the 'links' when the location of the 2 external database change?
Parfait
You would need the installed MSAccess.exe (Office app) to update paths using the Linked Table Manager on ribbon. Have at least one person (maybe you?) with the full Access app to update such links.