Select from 2 datasources

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vovan - 13 Nov 2007 13:09 GMT

I have 2 ADODB connections in VB 6 project - one to SQL Server 2000, another
one to Intuit QuickBooks data file via ODBC.
Is there a way to make selection from both, for instance (of cource it's not
working statement):

SELECT SQL.MyDatabase.Customer.CustomerID, SQL.MyDatabase.CustomerName,
QuickBooks.Customer.FirstName
FROM SQL.MyDatabase.Customer INNER JOIN
QuickBooks.Customer ON
SQL.MyDatabase.Customer.CustomerID = QuickBooks.Customer.CustomerID

I can imagine the only way in using temporary tables - After select from
QuickBooks everything is written into temp table in SQL and then a new
selection is done from the normal and temp tables.
Is there any way not to use temporary tables?

Thank you

vovan

Reply to this Message

Robert Morley - 13 Nov 2007 23:38 GMT

About the only other thing you could do would be to use ADO's dynamic recordsets to do it (see the Fields collection's Append
property). It's the same basic concept as using a temporary table, but you're matching up the data from both sources in-memory
entirely under your own control. This can potentially make it a lot faster than transferring data from one source to the other, but
since you have to match everything up yourself, it will also be a lot more work.

For most applications, I would suggest sticking with temporary tables on the SQL Server side of things.

Rob

>I have 2 ADODB connections in VB 6 project - one to SQL Server 2000, another one to Intuit QuickBooks data file via ODBC.
> Is there a way to make selection from both, for instance (of cource it's not working statement):
[quoted text clipped - 10 lines]
>
> vovan

Reply to this Message

VB Forum / Database Access / November 2007

Select from 2 datasources