PDA

View Full Version : SQL Query in VBA without using the Access database of OLEDB connection



mvandhu
12-27-2011, 08:23 AM
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
12-28-2011, 12:21 AM
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
12-30-2011, 09:00 AM
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
12-30-2011, 10:04 AM
If you use Thisworkbook.path in the code, it will always refer to the current workbook folder. (or use Thisworkbook.fullname)