querying text files with sql
 

Trying to run a SQL query with ADO involving 2 text files. This works when I
do it with MS Query, but I don't want to put data in a sheet but write a
recordset to a text file.
This is the code I have:

TextConn = "DSN=TextQuery;" 'system DSN

Query5 = "SELECT " & _
"E.ENTRY_ID, " & _
"E.PATIENT_ID, " & _
"E.ADDED_DATE, " & _
"E.READ_CODE " & _
"FROM " & _
"ENTRY.txt E INNER JOIN PATIENT.txt P ON " & _
"(P.PATIENT_ID = E.PATIENT_ID) " & _
"WHERE NOT E.READ_CODE = '' " & _
"ORDER BY 1 ASC"

Set rs = New ADODB.Recordset

rs.Open Source:=Query5, _
ActiveConnection:=TextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Now this fails with the error:
Data type mismatch in criteria expression.
Any idea what could be wrong here?
Have been looking for examples of this on the internet, but haven't found
anything useful sofar.
Thanks for any advice.


RBS

querying text files with sql
 

Forget about this. The code should be:

Query5 = "SELECT " & _
"E.ENTRY_ID, " & _
"E.PATIENT_ID, " & _
"E.ADDED_DATE, " & _
"E.READ_CODE " & _
"FROM " & _
"ENTRY.txt E INNER JOIN PATIENT.txt P ON " & _
"(P.PATIENT_ID = E.PATIENT_ID) " & _
"WHERE NOT E.READ_CODE IS NULL " & _
"ORDER BY 1 ASC"

And then it works fine.
This whole thing of querying text files with SQL is in fact quite nice. It
is quick and easy and is much better than doing it in a worksheet.


RBS


"RB Smissaert" wrote in message
...
Trying to run a SQL query with ADO involving 2 text files. This works when
I
do it with MS Query, but I don't want to put data in a sheet but write a
recordset to a text file.
This is the code I have:

TextConn = "DSN=TextQuery;" 'system DSN

Query5 = "SELECT " & _
"E.ENTRY_ID, " & _
"E.PATIENT_ID, " & _
"E.ADDED_DATE, " & _
"E.READ_CODE " & _
"FROM " & _
"ENTRY.txt E INNER JOIN PATIENT.txt P ON " & _
"(P.PATIENT_ID = E.PATIENT_ID) " & _
"WHERE NOT E.READ_CODE = '' " & _
"ORDER BY 1 ASC"

Set rs = New ADODB.Recordset

rs.Open Source:=Query5, _
ActiveConnection:=TextConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText

Now this fails with the error:
Data type mismatch in criteria expression.
Any idea what could be wrong here?
Have been looking for examples of this on the internet, but haven't found
anything useful sofar.
Thanks for any advice.


RBS