Why do you not want to use ADO, it is simple
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String
Dim myArray
Set oConn = CreateObject("ADODB.Connection")
oConn.CursorLocation = adUseClient
oConn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=123.456.78.90;" & _
"DATABASE=myDatabase;" & _
"USER=myAcc;" & _
"PASSWORD=myPassword;"
Set oRS = CreateObject("ADODB.Recordset")
sSQL = "Select acc_num FROM mytable WHERE acc_id=12121"
oRS.Open sSQL, oConn, adOpenStatic, adLockOptimistic
'load a worksheet
'load an array
my = oRS.GetRows()
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"bwreath " wrote in message
...
Hi,
thanks for the suggestion.
I have two questions actually.
1) This is the code that I have so far and I was wondering how
I would involve the concept of recordset.
2) My other question is that I want to use ODBC and not ADO.
I would like to know in the
Tools
References part of Excel what reference should I check off to be
able to use all of the ODBC objects and references?
Below is the code that I am using so far.
thanks very much.
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=myquery;DESC=MySQL ODBC 3.51 Driver
DSN;DATABASE=mydatabase;SERVER=123.456.78.90;UID=m yacc;PASSWORD=mypasswd;POR
T=3306;O"
_
), Array("PTION=3;STMT=;")), Destination:=Range("A1"))
CommandText = Array( _
"Select acc_num FROM mytable WHERE acc_id=12121" _
)
Name = "Query from myquery"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
BackgroundQuery = True
RefreshStyle = xlInsertDeleteCells
SavePassword = True
SaveData = True
AdjustColumnWidth = True
RefreshPeriod = 0
PreserveColumnInfo = True
Refresh BackgroundQuery:=False
End With
---
Message posted from http://www.ExcelForum.com/