Record set to array?

[mazzarin]

Is it possible to make a query using ado and have the data dumped into
an array for more manipulation? Right now I have a query like this:


strQuery = "SELECT baser, upt, multiplier FROM olb WHERE" & _
" SPR = '" & Range("C2").Value & "'" & _
" ORDER BY id desc"
With rs
.Open strQuery, dbConn, adOpenStatic
Range("J6").CopyFromRecordset rs
.Close

But I would rather do Array.Copyfromrecordset or something like that

The results of the query can be different sizes so I can't predefine
the array size, I think this requires declaring a variable as
'variant', but from there I'm not quite sure where to go.

Thanks for any assistance.

[Tim Williams]

dim arrRecs as variant

arrRecs = rs.GetRows()

--
Tim Williams
Palo Alto, CA


"mazzarin" <[email protected]> wrote in message news:[email protected]...
> Is it possible to make a query using ado and have the data dumped into
> an array for more manipulation? Right now I have a query like this:
>
>
> strQuery = "SELECT baser, upt, multiplier FROM olb WHERE" & _
> " SPR = '" & Range("C2").Value & "'" & _
> " ORDER BY id desc"
> With rs
> .Open strQuery, dbConn, adOpenStatic
> Range("J6").CopyFromRecordset rs
> .Close
>
> But I would rather do Array.Copyfromrecordset or something like that
>
> The results of the query can be different sizes so I can't predefine
> the array size, I think this requires declaring a variable as
> 'variant', but from there I'm not quite sure where to go.
>
> Thanks for any assistance.
>

[NickHK]

Depending what you are trying to achieve, you can also manipulate the RS
(depending on the cursor)
With RS.
.MoveFirst
Do while not .EOF
.Fields(1).Value=.Fields(1).Value+2
.NoveNext
Loop

But it would be better to optimise your SQL to return the correct info in
the first place.

NickHK

"mazzarin" <[email protected]> wrote in message
news:[email protected]...
> Is it possible to make a query using ado and have the data dumped into
> an array for more manipulation? Right now I have a query like this:
>
>
> strQuery = "SELECT baser, upt, multiplier FROM olb WHERE" & _
> " SPR = '" & Range("C2").Value & "'" & _
> " ORDER BY id desc"
> With rs
> .Open strQuery, dbConn, adOpenStatic
> Range("J6").CopyFromRecordset rs
> .Close
>
> But I would rather do Array.Copyfromrecordset or something like that
>
> The results of the query can be different sizes so I can't predefine
> the array size, I think this requires declaring a variable as
> 'variant', but from there I'm not quite sure where to go.
>
> Thanks for any assistance.
>