It wont work on ARRAYS but it will work on RANGES
if you use a (hidden) worksheet, and prevent calculation
screenupdating etc, you get the "memory" speed.

You probably had a look at Erlandsen's ADO tips already.
if not do so. Highly recommended.
http://www.erlandsendata.no/english/...php?t=envbadac





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"RB Smissaert" wrote:

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