Thanks, yes I can see it is all doable, although a very big job.
Also not sure if there could be a substantial performance drop, moving from
array
manipulations to Access tables manipulations as the second is disk-based.
I would gain on the text files SQL though as these have no indices.
Only possible bottleneck I can see for now is an array function that
aggregates rows on unique numbers in column one.
Not sure this can be done either directly in SQL or on an Access table(s) by
other means.
Probably the second one will be OK.
This is that function, with the helper array sort functions:
Function SwingArray(ByRef arr1 As Variant, _
ByRef colToTest As Long, _
ByRef DoSort As Boolean, _
ByRef StartCol As Long, _
Optional ByRef lDiscardLastCols As Long = 0) As Variant
'takes one multi-column 2D array and swings the elements
'that have the same value in colToTest to the row where
'this value was found first. Column colToTest will only
'hold unique values in the resulting array.
'StartCol is the column where the copying of the elements
'starts from.
'--------------------------------------------------------
Dim arr2()
Dim i As Long
Dim n As Long
Dim c As Long
Dim c2 As Long
Dim c3 As Long
Dim maxItems As Long
Dim uCo As Long
Dim LBR1 As Long
Dim UBR1 As Long
Dim LBC1 As Long
Dim UBC1 As Long
Dim tempIdx As Long
Dim arrError(0 To 0)
On Error GoTo ERROROUT
LBR1 = LBound(arr1, 1)
UBR1 = UBound(arr1, 1)
LBC1 = LBound(arr1, 2)
UBC1 = UBound(arr1, 2) - lDiscardLastCols
'adjust UBR1 to account for empty elements
'these empty element have to be at the
'bottom of the array if they are there
'-----------------------------------------
For i = LBR1 To UBR1
If arr1(i, colToTest) = Empty Then
UBR1 = i - 1
Exit For
End If
Next
'sorting the supplied array ascending
'------------------------------------
If DoSort = True Then
If PreSort2DArray(arr1, _
"A", _
colToTest) = False Then
On Error GoTo 0
SwingArray = False
Exit Function
End If
End If
'find and mark the doubles
'get the maximum number of doubles
'---------------------------------
tempIdx = arr1(LBR1, colToTest)
For i = LBR1 + 1 To UBR1
If Not arr1(i, colToTest) = tempIdx Then
tempIdx = arr1(i, colToTest)
uCo = uCo + 1
c2 = 0
Else
arr1(i, LBC1) = 0
c2 = c2 + 1
If c2 > maxItems Then
maxItems = c2
End If
End If
Next
'adjust the final array
'LBound will be as the original array
'------------------------------------
ReDim arr2(LBR1 To uCo + LBR1, _
LBC1 To (UBC1) + maxItems * (((UBC1 + 1) - StartCol)))
n = LBR1 - 1
'swing the elements from vertical to horizontal
'----------------------------------------------
For i = LBR1 To UBR1
If Not arr1(i, LBC1) = 0 Then
'copy first row in full
n = n + 1
For c = LBC1 To UBC1
arr2(n, c) = arr1(i, c)
Next
c3 = UBC1 + 1
Else
'copy subsequent rows from specified start column
'------------------------------------------------
For c = StartCol To UBC1
arr2(n, c3) = arr1(i, c)
c3 = c3 + 1
Next
End If
Next
SwingArray = arr2
On Error GoTo 0
Exit Function
ERROROUT:
arrError(0) = "ERROR"
SwingArray = arrError
On Error GoTo 0
End Function
Function PreSort2DArray(ByRef avArray, _
ByRef sOrder As String, _
ByRef iKey As Long, _
Optional ByRef iLow1 As Long = -1, _
Optional ByRef iHigh1 As Long = -1) As Boolean
If bValidLicense = False Then
Exit Function
End If
'the routine procSort2D can't handle large arrays
'causing an error out of stack space
'this is handled by sorting increasing larger parts
'of the array, so that there is less to be done when
'the whole array gets sorted
'---------------------------------------------------
Dim LR As Long
Dim lPreSorts As Long
Dim lArrayChunk As Long
Dim n As Long
LR = UBound(avArray)
'this value may depend on the hardware
'-------------------------------------
lArrayChunk = 8000
'no need to do pre-sorts
'-----------------------
If LR < lArrayChunk Then
PreSort2DArray = procSort2D(avArray, _
sOrder, _
iKey, _
iLow1, _
iHigh1)
Exit Function
End If
lPreSorts = LR \ lArrayChunk
For n = 0 To lPreSorts
If n < lPreSorts Then
'increase the part of the array in steps of lArrayChunk
'------------------------------------------------------
PreSort2DArray = procSort2D(avArray, _
sOrder, _
iKey, _
iLow1, _
(n + 1) * lArrayChunk)
Else
'sort the whole array
'--------------------
PreSort2DArray = procSort2D(avArray, _
sOrder, _
iKey, _
iLow1, _
iHigh1)
End If
Next
End Function
Function procSort2D(ByRef avArray, _
ByRef sOrder As String, _
ByRef iKey As Long, _
Optional ByRef iLow1 As Long = -1, _
Optional ByRef iHigh1 As Long = -1) As Boolean
Dim iLow2 As Long
Dim iHigh2 As Long
Dim i As Long
Dim vItem1 As Variant
Dim vItem2 As Variant
On Error GoTo ERROROUT
If iLow1 = -1 Then
iLow1 = LBound(avArray, 1)
End If
If iHigh1 = -1 Then
iHigh1 = UBound(avArray, 1)
End If
'Set new extremes to old extremes
iLow2 = iLow1
iHigh2 = iHigh1
'Get value of array item in middle of new extremes
vItem1 = avArray((iLow1 + iHigh1) \ 2, iKey)
'Loop for all the items in the array between the extremes
While iLow2 < iHigh2
If sOrder = "A" Then
'Find the first item that is greater than the mid-point item
While avArray(iLow2, iKey) < vItem1 And iLow2 < iHigh1
iLow2 = iLow2 + 1
Wend
'Find the last item that is less than the mid-point item
While avArray(iHigh2, iKey) > vItem1 And iHigh2 > iLow1
iHigh2 = iHigh2 - 1
Wend
Else
'Find the first item that is less than the mid-point item
While avArray(iLow2, iKey) > vItem1 And iLow2 < iHigh1
iLow2 = iLow2 + 1
Wend
'Find the last item that is greater than the mid-point item
While avArray(iHigh2, iKey) < vItem1 And iHigh2 > iLow1
iHigh2 = iHigh2 - 1
Wend
End If
'If the two items are in the wrong order, swap the rows
If iLow2 < iHigh2 Then
For i = LBound(avArray) To UBound(avArray, 2)
vItem2 = avArray(iLow2, i)
avArray(iLow2, i) = avArray(iHigh2, i)
avArray(iHigh2, i) = vItem2
Next
End If
'If the pointers are not together, advance to the next item
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9934
If iLow2 <= iHigh2 Then
iLow2 = iLow2 + 1
iHigh2 = iHigh2 - 1
End If
Wend
'Recurse to sort the lower half of the extremes
If iHigh2 > iLow1 Then procSort2D avArray, sOrder, iKey, iLow1, iHigh2
'Recurse to sort the upper half of the extremes
If iLow2 < iHigh1 Then procSort2D avArray, sOrder, iKey, iLow2, iHigh1
procSort2D = True
Exit Function
ERROROUT:
procSort2D = False
End Function
I will consider e-mailing you for professional help, but I can see it being
way too expensive.
RBS
"Pieter Wijnen"
wrote in message
news:e4EpFT%23eGHA.1320@TK2MSFTNGP04.phx.gbl...
> You're well on your way (seeing the post of yesterday)
> start by pushing the data to Access (as you do)
> Combine them into queries in Access
> You can then pull (link to) the data (queries) from Access into Excel for
> your users to view
> (Access Data can as you may know be read on any recent ms win version -
> the Engine is part of MDAC)
>
> If you require professional help you can drop me (or any of the pros an
> email)
> People here will continue giving free help for (not so) "mundane" tasks
>
> Pieter
>
> dba (dot) consult (at) g mail (dot) kom
>
> "RB Smissaert" wrote in message
> news:u$aKtJteGHA.3588@TK2MSFTNGP02.phx.gbl...
>> Have a commercial Excel .xla add-in with some helper VB6 ActiveX dll
>> files.
>> This is the frontend to a medical Interbase (5.6) database. Connection is
>> via ODBC.
>> It works with a treeview where the nodes in combination with other
>> controls like textboxes
>> and checkboxes etc. combine to run a search. After all the nodes have
>> run the combined data
>> is presented in 2 Excel sheets.
>> It relies heavily on array manipulations, but also partly on SQL, run on
>> text files.
>> All working very well. Main advantage of working with the arrays is that
>> it is very fast.
>> Running SQL on text files is amazingly fast as well. Drawbacks of this
>> approach are that it is complex
>> and that there can be a problem if the amount of produced data is very
>> big to the extent that the arrays
>> can't cope with it. So, maybe there could be an advantage to moving the
>> manipulations and possibly the
>> final data presentation to Access, although some customers don't have
>> Access installed, so the final
>> presentation may need to remain in Excel. Moving the manipulations to
>> Interbase (temp tables etc.) is not
>> an option.
>> Now this would be a major undertaking.
>>
>> Would any Access experts of this forum be willing/interested to have a
>> look at this, either for free or
>> commercially, just to give advice?
>> Keep in mind that this is a large application. The main .xla file,
>> stripped off any comments, tabs etc. is still
>> 3.9 Mb. My knowledge of Access is small, but I am familiar with SQL.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9934
>>
>> Thanks for any advice.
>>
>> RBS
>>
>
>
Our Donation Goal
: Why Donate? : Donate Today! : Donate Anonymously (ਗੁਪਤ) : Our Family of Supporters 
Linear Mode
