C# - Join tables from two different databases using different ODBC drivers

Question

I have a C# application which needs to pull data from two different databases: Database 1 is a MYOB database (which uses ODBC to connect using MYOB's driver) Database 2 is an Access database, again I'm using ODBC to connect to it.

Eg. this is the query I would like to run:

$query = "SELECT * FROM [" + myobConnectString + "].Accounts, ["+accessConnectString+"].tblTest WHERE tblTest.ID = Accounts.AccountID";

My question is how can I run this (or if it's even possible)?

Thanks!

Answer 1

In addition to the other answers you can use an ODBC Join Engine like the one at ODBC-ODBC Join Engine. However, this will pull the data locally and perform the join for you so it has similar disadvantages as Nickoli outlines at the end of his answer.