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" wrote in message
oups.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