Help with SQL and ODBC Text Driver
 

I have a tab delimited text file (over 300 variables, so I can't stick it
straight into another Excel workbook) and I am trying to use ODBC to connect
to the text file and pull values into an Excel file. The code I have so far
doesn't seem to be picking up the field headers. It'll run when I take off
the criteria, but will break when it comes to the first specific query for a
fieldname. Here's what I have. Can someone hlep please? Thanks a lot!

'===============================================
' start: open connection string to database with data
'===============================================
Dim cn As ADODB.Connection, rs As ADODB.Recordset, strSQL As
String

'!!! This line won't work cos I have the WHERE criteria referencing
a field
strSQL = "SELECT * FROM sourcedata.txt WHERE ACE07 = " & clientCode

' !!! This line will work, but after it opens the recordset, it
doesn't seem to find any fields
' plus I need the data to be specific, so I need the WHERE clause
strSQL = "SELECT * FROM sourcedata.txt"

strFolder = "H:\Excel-Powerpoint\Current\"

Set cn = New ADODB.Connection

cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;HDR=Yes"

If cn.State < adStateOpen Then Exit Sub

Set rs = New ADODB.Recordset

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

'==============================================
' start: transferring values to XLS source
'==============================================
sourceWS.Range("F4").Value = rs("Female")

[...more code to transfer values to XLS]

Help with SQL and ODBC Text Driver
 

Have a look he
http://tinyurl.com/2jm5p

RBS


"et10yl" wrote in message
...
I have a tab delimited text file (over 300 variables, so I can't stick it
straight into another Excel workbook) and I am trying to use ODBC to
connect
to the text file and pull values into an Excel file. The code I have so
far
doesn't seem to be picking up the field headers. It'll run when I take
off
the criteria, but will break when it comes to the first specific query for
a
fieldname. Here's what I have. Can someone hlep please? Thanks a lot!

'===============================================
' start: open connection string to database with data
'===============================================
Dim cn As ADODB.Connection, rs As ADODB.Recordset, strSQL As
String

'!!! This line won't work cos I have the WHERE criteria referencing
a field
strSQL = "SELECT * FROM sourcedata.txt WHERE ACE07 = " & clientCode

' !!! This line will work, but after it opens the recordset, it
doesn't seem to find any fields
' plus I need the data to be specific, so I need the WHERE clause
strSQL = "SELECT * FROM sourcedata.txt"

strFolder = "H:\Excel-Powerpoint\Current\"

Set cn = New ADODB.Connection

cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;HDR=Yes"

If cn.State < adStateOpen Then Exit Sub

Set rs = New ADODB.Recordset

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

'==============================================
' start: transferring values to XLS source
'==============================================
sourceWS.Range("F4").Value = rs("Female")

[...more code to transfer values to XLS]