Building a query without using a query table

[Glory]

http://www.excel-vba.com/vba-code-2-12-SQL.htm

I need to be able to run an SQL query without generating any result in my Excel worksheet. That's because I'll be returning so many rows, I won't be able to fit them onto a single sheet... I need to Inner Join multiple tables and then reference the result like an external database to create a Pivot Table.

eg:

Select * From C:\DOCUMENTS AND SETTINGS\DESKTOP\Book1.xls`.'Sheet1$'
UNION ALL C:\DOCUMENTS AND SETTINGS\DESKTOP\Book2.xls`.'Sheet1$'

All I need to know is how to modify code like what's at the link above to build a table that I can work with using SQL through VBA.

Can anyone help with that?

 

[Glory]

Can you give me a quick list of the absolutely necessary steps to do what I'm talking about? I can't get intellisense to recognize these objects and give me properties and methods. I look this up on MSDN and get a page full of confusion.

1) Create connection object and recordset object (msdn seems to indicate the recordset object is automatically created...? How do I reference it?)

2) .Open the connection, assign provider and ConnectionString.

-Assuming I use ACE, how do I know what the ConnectionString is? Sourcedatabase name, is this the name of each of my workbooks? Do I iterate through the setting of the connection string once for each new workbook?

etc.

 

[Glory]

I'm getting some of what I'm looking for from here, but I would appreciate a basic step-by-step overview.

 

[xenou]

The best help I have found is:
http://www.erlandsendata.no/english/index.php?t=envbadac
http://www.datawright.com.au/excel_resources/excel_filtering_with_ADO.htm
http://www.w3schools.com/ado/default.asp

For my part I read the chapter on ADO in Excel VBA programming published by Wrox Press.

The link you referred to as "confusion" is a .NET link so it's not 100% applicable to Excel VBA programming.

It takes time. Start slow. You are too far ahead of yourself. Query a single data source to get a simple query result returned. Don't start with UNION queries on multiple data sources. Make sure you understand SQL and fundamental database theory.

 

[Glory]

double post

 

[Glory]

Okay I hear you and I'll check those links out the next chance I get.

 

[Norie]

If you consolidate the data in one place you would have one data source rather than multiple data sources spread across multiple workbooks/worksheets.
Also, you would have to find a way to combine all the data at some point so you can use it for the pivot cache/table.

As for your code not working, the first thing tnt should check is the connection string. Eg provider/driver, paths etc

Even a simple typo in the connection string can cause a major problem.

Another thing to check is the SQL.

 

[Glory]

Norie: That's basically what the thrust of this thread was in the first place. So you're telling me the best way to do this is to create a macro that will combine all my data into an MDB file and assign a pivot-cache to my existing tables that will reference the MDB file?

I can't believe I can't find a simple answer to this question anywhere. Lots of people talk about having to deal with pivot tables from multiple sheets, but there are no straightforward answers about how to solve the problem. It's really absurd.

 

[Norie]

Sorry I don't quite follow.

What do you want to do exactly?

I steered away from this thread at first because of the title - it doesn't seem to reflect what you seem to trying to do.

It is a long thread I suppose, so perhaps I missed something.

 

[Glory]

I need to grab 100k+ rows from 5-10 workbooks and Pivot them.

I would prefer to use an invisible SQL "table" (a final 'Union' of an array of recordsets extracted from the 5-10 original workbooks) as the SourceData for my pivot table.

The link in the OP shows how it can be done... if I wanted to insert a querytable into a sheet. But the whole point is that the data won't fit onto a sheet.

What method would I use (eg querytables.add) to store final 'Union'-ed data so that a Pivot Table could reference the table?

 

[Norie]

What do you mean by an 'invisible' SQL table?