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

Now I remember the main reason why I opted for textfiles; the limitation of
65000 records.

RBS


"keepitcool" wrote in message
...
This works lightning fast for me.

but please read following page if you dont have excel 2000
http://support.microsoft.com:80/supp.../q246/3/35.asp

Sub XLasRS()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sC As String
Dim sQ As String
Dim i As Integer

sC = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & _
";Extended Properties=Excel 8.0;"

sQ = " SELECT a.acctnr, b.period, a.acctname," & _
" a.linenr, l.linename, b.amount" & _
" FROM accounts a, balances b , lines l" & _
" WHERE a.linenr = l.linenr AND b.acctnr = a.acctnr"

'Connect
Set cn = New ADODB.Connection
cn.Open sC
'Retrieve
Set rs = New ADODB.Recordset
rs.Open sQ, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
' optional ways of retrieving a recordset
' Set rs = cn.Execute("[A1:Z1000]") ' first worksheet
' Set rs = cn.Execute("[DefinedRangeName]") ' any worksheet
'Write
Application.ScreenUpdating = False
With Worksheets(1)
.Cells.Clear
For i = 1 To rs.Fields.Count
.Cells(1, i).Value = rs.Fields(i - 1).Name
Next
.Cells(2, 1).CopyFromRecordset rs
End With
Application.ScreenUpdating = True
End Sub




keepITcool

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


"RB Smissaert" wrote:

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

Why not store your data in a database?

P


"RB Smissaert" wrote in message
...
Now I remember the main reason why I opted for textfiles; the limitation
of
65000 records.

RBS

Because we have no permission from the company that supplies the software to
write to the database.

RBS

"Phobos" wrote in message
...
Why not store your data in a database?

P


"RB Smissaert" wrote in message
...
Now I remember the main reason why I opted for textfiles; the limitation
of
65000 records.

RBS

Just when I sent the previous e-mail off I realised that I could just write
to Access. The main database is Interbase.

RBS

"Phobos" wrote in message
...
Why not store your data in a database?

P


"RB Smissaert" wrote in message
...
Now I remember the main reason why I opted for textfiles; the limitation
of
65000 records.

RBS

Just realised that many users don't have Access. I suppose I could install
mySQL, but it gets a bit complicated then. Think I will stick with text
files which works really well.

RBS

"Phobos" wrote in message
...
Why not store your data in a database?

P


"RB Smissaert" wrote in message
...
Now I remember the main reason why I opted for textfiles; the limitation
of
65000 records.

RBS

Bart,

to store the raw data i'd use a database format (.mdb)
AND set a few indexes...

using a textfile is an option.. though probably not the one i'd choose.
and certainly not when i have a lot of data and i need to join tables.



keepITcool

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


"RB Smissaert" wrote:

Now I remember the main reason why I opted for textfiles; the
limitation of 65000 records.

RBS

Your users DONT need MS-Access

You can do some setup and maintanence in access, but the users just need
ODBC drivers in order to enable Excel to access the mdb files.



keepITcool

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


"RB Smissaert" wrote:

Just realised that many users don't have Access. I suppose I could
install mySQL, but it gets a bit complicated then. Think I will stick
with text files which works really well.

RBS

"Phobos" wrote in message
...
Why not store your data in a database?

P


"RB Smissaert" wrote in message
...
Now I remember the main reason why I opted for textfiles; the
limitation
of
65000 records.

RBS

yep, that's what i'm saying.

Phobos is arguing that installing the jet engine = MDAC is a pain.
(see different branch in this thread)

But I read that you'll be using this on a corporate network, so that
(limited) pain is easily passed to the network boys, and is probably no
pain at all, cuz on a well maintained corporate install it's probably
there already.

Have fun.


keepITcool

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


"RB Smissaert" wrote:

Will give it a go. MDAC is already there.

RBS

"keepitcool" wrote in message
...
yep, that's what i'm saying.

Phobos is arguing that installing the jet engine = MDAC is a pain.
(see different branch in this thread)

But I read that you'll be using this on a corporate network, so that
(limited) pain is easily passed to the network boys, and is probably no
pain at all, cuz on a well maintained corporate install it's probably
there already.

Have fun.


keepITcool

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


"RB Smissaert" wrote:

"keepitcool" wrote in message
...
yep, that's what i'm saying.

Phobos is arguing that installing the jet engine = MDAC is a pain.
(see different branch in this thread)

But I read that you'll be using this on a corporate network, so that
(limited) pain is easily passed to the network boys, and is probably no
pain at all, cuz on a well maintained corporate install it's probably
there already.

I suppose I should clarify my position here <g

If MDAC has to be installed on all machines that the app is to run on, and
the developer does not know at design time which machines it will be run on
then users will complain of errors/bugs.

If the required functionality can be achieved with text files, then this
pain is avoided.

The best way IMHO is to use MDAC in this situation.

P