I'm actually selecting field1, field2 and field3, and then using the
contents of field1 to obtain another set of data to join with this.

I'd very much appreciate an example of using the collection of arrays since
that's the method that's been eluding me so far.




"RB Smissaert" wrote in message
...
I would go with a single array like this:

Sub TEST()

Dim rs As ADODB.Recordset
Dim strQuery As String
Dim arr

strQuery = "SELECT FIELD1 FROM TABLE1 WHERE FIELD1 = 10"

Set rs = New ADODB.Recordset

rs.Open Source:=strQuery, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

If Not rs.EOF Then
arr = rs.GetRows
rs.Close
Set rs = Nothing
End If

End Sub


RBS


"Steve" wrote in message
...
Hello,

If you've got an example of copying the recordset into a collection of
array's that'd be most appreciated since I'm struggling to get my head
around such a structure in VBA. Having an example may make things a lot
clearer.


Many thanks in advance,


Steve

"RB Smissaert" wrote in message
...
You could use an array or a collection of arrays, where the arrays hold
one row.
Can see no reason though why it wouldn't work with an array.
What is the precise problem you have?

RBS

"Steve" wrote in message
...
Hello

I've been trying to figure out how to do the following:

Reading data from one database table into memory and then querying a
table within another database based upon the contents of the first
dataset, then placing the end result (a selection of fields from both
tables) into a sheet within the spreadsheet.

I was hoping to use a collection object to represent the data read from
the first table, and then using data from one field construct a query
to read the data from another. However I've not been able to find any
examples of using collections in such a way, which makes me wonder
whether it's possible ?

Can I have a collection of which mimics a table such that I can work my
way through each column by name, and each row read by number ?


Thanks in advance


Steve