Manipulating ADO Recordsets in Excel

[LFB]

Hi all,

I'm using Excel 2003 and Win Xp sp2 to connect to SQL server 8 to return a recordset. Great tool because I can group the data before getting into xl which greatly reduces the number of records I need to retrieve.

Problem is I need to do another grouping on the recordset itself. I don't want to have to bring it into Excel and perform a sub total or consolidate on it because it'll be too slow.

Does anyone know a way of doing this?

I tried putting the data into a temp table so that I could perform a new connection to that table and group it on just the fields I needed but it causes errors.

Part of the problem is I'm using a component from the ERP system my client uses to connect to SQL server and it has limited functionality. The best I can do is return this bloated recordset due to user access rights. I then want to strip it down and consolidate the data but do it in the recordset not in Excel.

Any suggestions would be appreciated.

Cheers

 

[SkipVought]

Hi,

You have control of the RecordSet Object. Depending on the way that you accessed the database, dynamic, forward, keyset, static, you may or may not be able to traverse the rst many times while it's open.

Skip,
[sub]
[red][/red]
[/sub]

 

[LFB]

Its an adUseClient connection so: static.

What I'd really like to do is grab the recordset, copy it into memory on the client and then perform aggregate functions on it, group, sum etc. Since I'm dealing with potentially thousands of records I'd like to avoid dropping it into an xl worksheet unless I absolutely have to.

What I don't know how to do is perform aggregate functions on a recordset. Is it even possible?

 

[JerryKlmns]

What I don't know how to do is perform aggregate functions on a recordset. Is it even possible?

Nope.-

[blue]IF[/blue] this a regular job [blue]THEN[/blue]
create a stored procedure in SQL server to do the aggregate functions and use that
[blue]ELSE[/blue]
open a new recordset and use an appropiate sql statement to do the aggregate functions
[blue]END IF[/blue]

The second is heavy and time consuming.