run SQL queries on arrays?

[RB Smissaert]

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

 

[keepitcool]

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/index.php?t=envbadac





keepITcool

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

 

[RB Smissaert]

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

RBS

 

[RB Smissaert]

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

RBS

This works lightning fast for me.

but please read following page if you dont have excel 2000
http://support.microsoft.com:80/support/kb/articles/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 >

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

RBS

 

[Phobos]

Why not store your data in a database?

P

 

[RB Smissaert]

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

RBS

 

[RB Smissaert]

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

RBS

 

[RB Smissaert]

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

 

[keepitcool]

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 >

 

[keepitcool]

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 >

 

[keepitcool]

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" <[email protected]> wrote:

 

[RB Smissaert]

Will give it a go. MDAC is already there.

RBS

 

[Phobos]

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