Perform SQL command on Disconnected ADO Recordset

[R Avery]

I have an ADO recordset that I created by declaring it as new and
adding all of the columns and data manually. I want to be able to
perform a SQL string on it to do aggregation and other things that SQL
provides. Is there anyway to do this?

[K Dales]

A recordset is the result of a query, so you can't really query it. You will
need to use code to extract the values you need (or to step through and
calculate your aggregates). This is a bit of a hassle, but not too bad. The
Filter property can help here, as well as the Find method. Then a simple
loop through the recordset can retrieve or add or otherwise manipulate the
values.

"R Avery" wrote:

> I have an ADO recordset that I created by declaring it as new and
> adding all of the columns and data manually. I want to be able to
> perform a SQL string on it to do aggregation and other things that SQL
> provides. Is there anyway to do this?
>
>

[Robin Hammond]

I tend to use ADO against a database connection, but I believe that you can
also use it to query a closed workbook, so

1. where is the data coming from? if from a workbook in a reasonable data
structure, you could just query the closed workbook.
2. if not, you could use copyfromrecordset to put the data in a workbook,
save the book, then query the book.

I don't have time to work up an example right now I'm afraid, but the
suggestion might get you headed in the right direction.

Robin Hammond
www.enhanceddatasystems.com


"K Dales" <[email protected]> wrote in message
news:[email protected]...
>A recordset is the result of a query, so you can't really query it. You
>will
> need to use code to extract the values you need (or to step through and
> calculate your aggregates). This is a bit of a hassle, but not too bad.
> The
> Filter property can help here, as well as the Find method. Then a simple
> loop through the recordset can retrieve or add or otherwise manipulate the
> values.
>
> "R Avery" wrote:
>
>> I have an ADO recordset that I created by declaring it as new and
>> adding all of the columns and data manually. I want to be able to
>> perform a SQL string on it to do aggregation and other things that SQL
>> provides. Is there anyway to do this?
>>
>>

[R Avery]

The data is coming from an open workbook, and the range i want to get
it from can start with any upperleft cell. The purpose of this is to
have all data centralized in a single place, and just create random
QueryRange() views on different worksheets.