Database access & querying

[Steve]

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

 

[RB Smissaert]

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]

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]

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]

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]

Not sure really why you want to do that, but once you have the big array
(named arr) you could do:

dim arrTemp
dim i as long
dim c as long
dim Coll as Collection

Set Coll = New Collection

for i = lbound(arr) to ubound(arr)
for c = lbound(arr) to ubound(arr, 2)
arrTemp = arr(i, c)
next
Coll.Add arrTemp
next


RBS

 

[RB Smissaert]

Sorry, you would have to dimension arrTemp:

redim arrTemp(0 to ubound(arr))

RBS

 

[Steve]

Thanks RBS.

I've used the array method for now since it's quicker to put together,
though I'd really like to be able to refer to the data by field name

In perl I could do something like:

recordset [index] {'fieldname'}

Thanks.


Steve