Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel (xls) files spontaneously converting to text files | Excel Discussion (Misc queries) | |||
Querying Worksheets | Excel Worksheet Functions | |||
Querying data | Excel Discussion (Misc queries) | |||
Querying 2 Excel files | Excel Worksheet Functions | |||
QUERYING ACCESS | Excel Discussion (Misc queries) |