RBS,

Thanks a lot for your code. I will test it one my data and will let you
know in case I have any syntax problems.

Regards,
HP
India


RB Smissaert wrote:
> Have tested now and this works fine:
>
> Sub test()
>
> Dim rs As ADODB.Recordset
> Dim TempTextConn As String
> Dim strQuery As String
>
> TempTextConn = _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & _
> "C:\TempTables\;" & _
> "Extended Properties=Text;"
>
> 'query example :
> '---------------
> strQuery = "SELECT " & _
> "P.PATIENT_ID, " & _
> "P.OLD_EXTERNAL_NO, " & _
> "P.FORENAME_1, " & _
> "P.SURNAME, " & _
> "P.AGE, " & _
> "P.GENDER_TYPE, " & _
> "P.ADDRESS_LINE_2, " & _
> "P.REGISTERED_GP, " & _
> "E.READ_CODE, " & _
> "E.START_DATE, " & _
> "E.ADDED_DATE " & _
> "INTO ResultFile.txt IN " & _
> "'C:\TempTables\' " & _
> "'Text;FMT=Delimited' " & _
> "FROM " & _
> "2IDALL.txt P INNER JOIN 3Morb_FULL.txt E ON " & _
> "(P.PATIENT_ID = E.PATIENT_ID) "
>
> Set rs = New ADODB.Recordset
>
> rs.Open Source:=strQuery, _
> ActiveConnection:=TempTextConn, _
> CursorType:=adOpenForwardOnly, _
> LockType:=adLockReadOnly, _
> Options:=adCmdText
>
> 'can't close RecordSet here as it is closed already
> Set rs = Nothing
>
> End Sub
>
>
> RBS
>
>
> "Hari" <excel_hari@yahoo.com> wrote in message
> news:1148276840.305676.110210@j73g2000cwa.googlegroups.com...
> > RBS,
> >
> > Thanks for your response. I have used SQL within SAS (and in a small
> > way in Oracle) but havent come across where Joins are done directly on
> > text files. I would like to learn this method of yours. When you say
> > text file, can it be done on CSV format files?
> >
> > (Just to clue you in, as to how I was planning to approach this was by
> > doing an non-equi join to produce a table which doesnt have te lookup
> > values and secondly an equi join to produce a table which contains the
> > lookup values, then I was planning to stack these tables one below the
> > other with an additional column indicating as to what table they are
> > from.)
> >
> > Regards,
> > HP
> > India
> >