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

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

Did this already, but found it was faster with text files.

RBS

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