Viewing the result of an sql query in excel.

[Guest]

Hi I try this Q here instead since i was recommended this:

"Hi Kjell
There used to be a way to dynamically link Excel to databases, but I think
MS had to remove it due to some kind of lawsuit. Best would be to ask this in
the Excel.programming newsgroup, as the specialists there will know for sure.
-- Cindy Meister (Word MVP)"
:

My QUESTION:

Hi.
Background:
I have created an add-in for excel that opens a form that lets the user
graphically select what data he wants to view. The result of the input is
formatted as an sql string, and I want to show the result of the query in an
excel document.
* the result file i quite big. 256*100 000 rows i might add

I am considering the following:

1. Copy data from the Sql server to an Excel sheet. Something like this:

Sheet1.Range("A1").CopyFromRecordset myrecordset

2. Is there a way, programmatically, to let excel directly show the result
from the sql-query through a "view-connection " to the database? So to say
that Excel always shows what data the sql server contains.

I would prefer the last solution if that is possible.

Or is there any other smart way to do this?

Best Regards Kjell Nilsson

 

[Guest]

The Giant Killer here is this one little line:
* the result file i quite big. 256*100 000 rows i might add.

Now I read that one of 2 ways (neither of which is very good):
256 columns by 100,000 rows
or
256 groups of 100,000 rows

Prior to Excel 2007, the maximum number of rows on an Excel spreadsheet is
65,536. Excel 2007 will handle 2^20 rows (1,045,576 rows) per sheet. With
this in mind, if your 256x100,000 is columns by rows, Excel 2007 could handle
the load, but if it is 256*100,000 = 25,600,000 rows, then you're going to
need Excel and 26 worksheets in it and some special processing to split the
data across the sheets [I happen to have just such an Excel tool already
built ... but it reads from a .txt, .dat or .csv type file= :-( ]

Because of this limitation I'm going to say that even the answer to #2 is
no. Excel really doesn't do it's data retrieval 'live' and in real time.
It's more like returning a snapshot recordset using the SQL statement.

 

[NickHK]

To add to other comments:
I would question the worth of showing the user that much data. Would they
actually look at it all ?
Provide the user some means to filter the results and only display those of
interest. That would greatly simplify you task and provide the user with
<useful> data. There is no point in just duplicating such a large dataset
and requiring XL2007 for it to work.

As for updating the data, you can use a database query and set the fresh
period to some suitable period. Whilst I would not advise a 'live" update
(<30 sec), every 5 minutes or so should be OK, depending on amount of data
and network/server speeds.

NickHK