VBA/SQL query

[rjre]

Not really too sure if this is the correct forum but...here goes:

I have created my connection MySQL with the following code:

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=DATABASE1;" & _
"USER=USER1" & _
"PASSWORD=PASSWORD1;" & _
"Option=3"
End Sub

and am planning to extract the data with:

Private Sub PullData()
Set rs = New ADODB.Recordset
ConnectDB
strSQL = "select FIELD1, FIELD2, FIELD3 from DATABASE1.TABLE1 where
FIELD4='xxx' order by date;"
'rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
End Sub

i do not receive any errors when i run it so i presume all ok.

my questions are:
how do i pass the variable xxx from a cell in excel?
how do i even return this data into excel once i have it?

thanks very much all

richard

 

[Bob Phillips]

--
__________________________________
HTH

Bob

Not really too sure if this is the correct forum but...here goes:

I have created my connection MySQL with the following code:

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub ConnectDB()
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=DATABASE1;" & _
"USER=USER1" & _
"PASSWORD=PASSWORD1;" & _
"Option=3"
End Sub

and am planning to extract the data with:

Private Sub PullData()
Set rs = New ADODB.Recordset
ConnectDB
strSQL = "select FIELD1, FIELD2, FIELD3 from DATABASE1.TABLE1 where
FIELD4='xxx' order by date;"
'rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
End Sub

i do not receive any errors when i run it so i presume all ok.

my questions are:
how do i pass the variable xxx from a cell in excel?

Private Sub PullData()
Set rs = New ADODB.Recordset
ConnectDB
strSQL = "select FIELD1, FIELD2, FIELD3 from DATABASE1.TABLE1 " & _
"where FIELD4='" & Range("A1").value & "' order by date;"
'rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
End Sub

how do i even return this data into excel once i have it?

ary = rs.GetRows
Range("B2") = ary

 

[rjre]

--
__________________________________
HTH

Bob














Private Sub PullData()
Set rs = New ADODB.Recordset
ConnectDB
strSQL = "select FIELD1, FIELD2, FIELD3 from DATABASE1.TABLE1 " & _
                "where FIELD4='" & Range("A1").value & "' order by date;"
'rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
End Sub


    ary = rs.GetRows
    Range("B2") = ary- Hide quoted text -

- Show quoted text -

perfect. thanks!