Can't select field in ADO with Text Driver
 

In Excel 2003, I'm using ADO to read a CSV text file. It works fine
unless I try to specify a specific field in the SQL statement:

Function GetList()

Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Dim fd As ADODB.Field
Dim myarrray() As String

Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection

conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & ThisWorkbook.Path & ";"

'this works great, BUT I need to be able to get just one field..
'rs.Open "select * from test.csv", conn

'this returns the error "Too few parameters. Expected 1.":
Set rs = conn.Execute("select * from test.csv where fieldname =
'Field2'")

'this works great, BUT I need to be able to get just one field..
'Set rs = conn.Execute("select * from test.csv")

myarray = rs.GetRows

Set rs = Nothing
Set conn = Nothing
End Function


In the CSV file, the first row is the field names:

Field1,Field2,Field3
Rec1Fld1,Rec1Fld2,Rec1Fld3
Rec2Fld1,Rec2Fld2,Rec2Fld3
Rec3Fld1,Rec3Fld2,Rec3Fld3


What do I need to do to return just the one field?



Thanks,

Greg

Can't select field in ADO with Text Driver
 

"select Field2 from test.csv"


Tim

"Greg Lovern" wrote in message
ups.com...
In Excel 2003, I'm using ADO to read a CSV text file. It works fine
unless I try to specify a specific field in the SQL statement:

Function GetList()

Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection
Dim fd As ADODB.Field
Dim myarrray() As String

Set rs = New ADODB.Recordset
Set conn = New ADODB.Connection

conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=" & ThisWorkbook.Path & ";"

'this works great, BUT I need to be able to get just one field..
'rs.Open "select * from test.csv", conn

'this returns the error "Too few parameters. Expected 1.":
Set rs = conn.Execute("select * from test.csv where fieldname =
'Field2'")

'this works great, BUT I need to be able to get just one field..
'Set rs = conn.Execute("select * from test.csv")

myarray = rs.GetRows

Set rs = Nothing
Set conn = Nothing
End Function


In the CSV file, the first row is the field names:

Field1,Field2,Field3
Rec1Fld1,Rec1Fld2,Rec1Fld3
Rec2Fld1,Rec2Fld2,Rec2Fld3
Rec3Fld1,Rec3Fld2,Rec3Fld3


What do I need to do to return just the one field?



Thanks,

Greg

Can't select field in ADO with Text Driver
 

Thanks!

Greg