.CopyFromRecordset into variable array rather than to worksheet?

I'm using the following to pull data from an Access database to Excel, however, rather than pulling it all to an empty sheet and then loading each cells value into my array, is it possible to pull data directly to the array?

Please Login or Register  to view this content.

Be a pal and gimme a clue how? MS help isn't being very helpful on the GetRows method. Seems there's a big difference between ADO getrows and DAO getrows which has been confusing me for the last 15 minutes.

I'm missing something cos when I try to read back the array all I get is errors.

Please Login or Register  to view this content.

How about just 1?

Please Login or Register  to view this content.

It's not reading anything into the array, msgbox Ubound(Products) always displays '0' and msgbox Products(0) results in a subscript out of range.

I know for sure the SQL statement is returning data as if I use:

Please Login or Register  to view this content.

it works just fine.

Imagine you're talking to a complete noob.

How do I access each individual value in the array?
Why does Ubound(Products) always display '0'? I thought Ubound is supposed to return the highest position in the array?

It should be reading in 350 odd product codes...

Disregard.

Got it. For a non programmer to visualise loading 1 data field into a 2 column array.....well it's weird.

However, Products(0,whatever) is working.

Thanks for the help