Using Excel 2002. Discovered some months ago that you can run SQL queries on
simple text files like this:

Public Const TextConn As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test\;" & _
"Extended Properties=Text;"

Public Const TestFolder As String = _
"C:\Test\"

Query = "SELECT " & _
"E.PATIENT_ID, " & _
"E.ADDED_DATE, " & _
"E.READ_CODE, " & _
"E.CHILD_ENTRY_ID2, " & _
"EA.NUMERIC_VALUE " & _
"INTO ENTRY4.txt " & _
"IN '" & TestFolder & "' " & _
"'Text;FMT=Delimited' " & _
"FROM " & _
"ENTRY3.txt E " & _
"INNER JOIN ENTRY_ATTRIBUTE.txt EA ON " & _
"(E.CHILD_ENTRY_ID1 = EA.ENTRY_ID)"

Set rs = New ADODB.Recordset

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

This works really nice and fast, but as I don't really need the text files
would it somehow be possible to do this with VBA arrays rather than text
files? The arrays could have exactly the same layout as the text files,
including the field headers. As arrays are in memory it might be even faster
than working with text files.
Thanks for any advice.


RBS