put query result into variable
 

hi hope someone can help
i can connect to oracle through excel no problem
the problem i am having is i want to put the result of the following query
into a varable
at the moment i return the result into a cell on a excel worksheet then use
the value in the cell for a second query
here is the query i use

Sub form_a()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim col As Integer
Dim data_to_find As Variant
Dim cttab As String

cnn.Open "Driver={microsoft odbc for oracle};" & _
"Server=EASY ;" & _
"Uid=easy;" & _
"Pwd=easy;"




sql = " select distinct code"
sql = sql & " from history"
sql = sql & " where trunc(date_time) = trunc(sysdate-1)"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly
Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
cttab = "CT_" & Cells(1, 1).Value

rst.Close

sql = " select brand from " & cttab & " "
sql = sql & " where trunc(calldate) = trunc(sysdate-1)"
sql = sql & " and brand is not null"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly


Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
rst.Close


End Sub

put query result into variable
 

Depending on if you expect one or more rows your would do:

dim vVar

vVar = rst.GetRows

or

vVar = rst.Fields(0)


RBS


"andy" wrote in message
...
hi hope someone can help
i can connect to oracle through excel no problem
the problem i am having is i want to put the result of the following query
into a varable
at the moment i return the result into a cell on a excel worksheet then
use the value in the cell for a second query
here is the query i use

Sub form_a()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim col As Integer
Dim data_to_find As Variant
Dim cttab As String

cnn.Open "Driver={microsoft odbc for oracle};" & _
"Server=EASY ;" & _
"Uid=easy;" & _
"Pwd=easy;"




sql = " select distinct code"
sql = sql & " from history"
sql = sql & " where trunc(date_time) = trunc(sysdate-1)"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly
Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
cttab = "CT_" & Cells(1, 1).Value

rst.Close

sql = " select brand from " & cttab & " "
sql = sql & " where trunc(calldate) = trunc(sysdate-1)"
sql = sql & " and brand is not null"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly


Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
rst.Close


End Sub