Database access & querying
 

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

Database access & querying
 

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

Database access & querying
 

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

Database access & querying
 

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

Database access & querying
 

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

Database access & querying
 

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


"Steve" wrote in message
...
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

Database access & querying
 

Sorry, you would have to dimension arrTemp:

redim arrTemp(0 to ubound(arr))

RBS


"RB Smissaert" wrote in message
...
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


"Steve" wrote in message
...
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

Database access & querying
 

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


"RB Smissaert" wrote in message
...
Sorry, you would have to dimension arrTemp:

redim arrTemp(0 to ubound(arr))

RBS


"RB Smissaert" wrote in message
...
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


"Steve" wrote in message
...
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