Viewing the result of an sql query in excel.
 

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

Viewing the result of an sql query in excel.
 

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.



"kjell nilsson" wrote:

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

Viewing the result of an sql query in excel.
 

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

"kjell nilsson" wrote in message
...
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