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]

By an 'invisible SQL table', I'm just fumbling for a word that means something that will never be seen by the user.

A query table is a visible object that has to be inserted into a sheet. So it has all the limitations that come with using a worksheet (65k max rows in particular).

I need a way to store the information and make it my PivotCache source without actually putting it into a sheet. I know you mentioned an .mdb file earlier. Are there other possible soutions?

 

[Norie]

Your use of 'invisible' query is perhaps confusing - I know it was for me.

I actually thought you wanted to store the data without using a database/worksheet - to me anyway.

The only thing I could think of along those lines would be a disconnected recordset.

Anyway, so you just want to be able to consolidate the data from all the worksheets and use that for the data source for the pivot table?

And, of course, not let the user see the source?

Is that right?

Would storing the data in Access be an option?

If it is I think I might have a couple of ideas.

I'll check in later and post back if I can help.

 

[Glory]

It doesn't confront me if the user sees the source.

None of these ADO sites address cases where the data is too big for a sheet... where else it's possible to put it, how to 'disconnect' a recordset.

I'm concerned with finding a way to get a Pivot Table to access a table of data that can't fit onto a single sheet.

MS Access isn't off the table... I'll use it if there are absolutely no other options.

Do I have any options for doing this with Excel alone?

 

[Norie]

Well I suppose you could out the data in a database or even a text file.

The reason you probably don't see much about row limitations using ADO with Excel is because it's usually used to 'pull' not 'push' data from/to Excel.

So unless you want to use a text file, which might not be ideal, why not use Access?

You probably should have looked into that as soon as you realised the amount of data you would be working with wouldn't fit into Excel.

PS You can probably replace 'Access' with any other database you have available.

 

[xenou]

Actually, it's possible a pivot cache can hold data limited only by available memory/disk space. As far as whether it's visible or not, I'll go along with the latter as I have no idea how one could ever "look" at one. For my part, I don't find it pleasant or convenient to use multiple data sources for a pivot table. It might be easier to centralize the data in Access and source the pivot table from the single data store, to simplify the process.

 

[xenou]

None of these ADO sites address cases where the data is too big for a sheet... where else it's possible to put it, how to 'disconnect' a recordset.

Recordsets are not permanent objects. They can be very large (millions of records I guess). But they only last while you are actually using them. To save the data they must be committed to a permanent storage media (i.e., a database).

Note that if you refreshed a pivot cache from a recordset that (as far as I know), the pivot cache is a separate (and permanent) data store within Excel. So at that point you could release the recordset having done its job.

 

[xenou]

None of these ADO sites address cases where the data is too big for a sheet... where else it's possible to put it, how to 'disconnect' a recordset.

Disconnected recordsets is a separate topic. I would not worry about that for now.

 

[Glory]

xenou: Is there a way to "join" recordsets the way it's possible to join tables?

To be perfectly honest, I'm becoming less and less thrilled with the idea of working with abstract recordsets, after a frustrating experience with MS Query last night.

So unless you want to use a text file, which might not be ideal, why not use Access?

I actually woke up this morning thinking that I should use a .CSV file.

Can I get ADO from the final workbook to:

1) Grab data from my 5-10 data workbooks...
2) Compile all the rows into a single .CSV file by adding to that file one workbook at a time, and then...
3) Reference that .CSV file from a pivot table in the final workbook?

 

[Glory]

On Error Resume Next
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
 
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
 
strPathtoTextFile = "C:\Databases\"
 
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathtoTextFile & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited"""
 
objRecordset.Open "SELECT * FROM PhoneList.csv", _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText
 
Do Until objRecordset.EOF
    Wscript.Echo "Name: " & objRecordset.Fields.Item("Name")
    Wscript.Echo "Department: " & _
        objRecordset.Fields.Item("Department")
    Wscript.Echo "Extension: " & objRecordset.Fields.Item("Extension")   
    objRecordset.MoveNext
Loop

From here.

This explains (sort of) how to get values from a text file.

Now I need to know how to actually create a .CSV file using ADO, and populate that file with data from a number of .XLS workbooks...

Edit: Something like this? Can you guys help me parse this stuff so I know what does what?

Dim csvdata As String
Dim CRLF As String
Dim objStream As Object
 
CRLF = Chr(13) & Chr(10)
 
csvdata = """key"",""value""" + CRLF
csvdata = csvdata + """a"",""a""" + CRLF
csvdata = csvdata + """aacute"",""á""" + CRLF
 
Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Position = 0
objStream.Charset = "UTF-8"
objStream.WriteText csvdata
objStream.SaveToFile "test.csv", 2 ' adSaveCreateOverWrite
objStream.Close

 

[Norie]

I suppose you could use a CSV file but it would probably be easier to use a database as a 'repository' for the data.

One thought I had was to use ADO to get the data from the workbooks but also summarize it in some way.

Sort of like giving the pivot table a kick start I suppose.

I don't know enough about your data to say if it's a viable option.

If you could do it you might be able to get all the data, summarized of course onto a worksheet.

The sort of summarizing I was thinking of was just simple things that can be done with SQL, like grouping, counting, a bit of summing...