SQL Query in VBA without using the Access database of OLEDB connection

[mvandhu]

Hi All,
I have some clarifications on using SQl queries in VBA.
I have a workbook with 3 sheets. The first 2 are the input sheets. Based on some key columns (say 2 column values) I have perform left outer join (or any join operation) on the 2 input worksheets. I searched in many sites and all the sites/forum used OLEDB connection (used Access or specified the path where the workbook resides). So my question is- can I use SQL join query on these 2 input worksheets without using that OLEDB connection or Access DB?
If so can you specify the syntax or sample code for a join operation?

[Aflatoon]

You can use ADO but you have to specify a path and use an OLEDB provider. Why is that a problem?

I should advise that there are memory leak issues with querying an open workbook with ADO.

[mvandhu]

I want to create an automated template for the above mentioned scenario and this template will be upload in a central repository of our proj. So user who want to perform the above mentioned scenario will download it from the central repository and may save it any were in their desktop and use this excel. So the problem is with path specification. Is it possible to use SQL query in VBA without specifying this path to perform any action with the worksheets (eg. join operation on 2 worksheets based on a key. etc) .

[Aflatoon]

If you use Thisworkbook.path in the code, it will always refer to the current workbook folder. (or use Thisworkbook.fullname)