Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Project advice

Discussion in 'Information Technology' started by RB Smissaert, Jul 28, 2006.

Tags:
  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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.

    Thanks for any advice.

    RBS
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News 'The Sikh Project' Book Brings Photo Exhibit Portraits Home - NBCNews.com Breaking News Sep 16, 2016
    Sikh News Life After 9/11: NY 'Sikh Project' To Highlight Community Heroes - Chandigarh Tribune Breaking News Sep 12, 2016
    Sikh Coalition The Sikh Project Sikh Organisations Jun 21, 2016
    Spn Project: Ardaas In Focus Sikh Sikhi Sikhism Feb 22, 2016
    Arts/Society 'The Singh Project': British photographers celebrate identity of Sikh men Language, Arts & Culture Jun 20, 2014

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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" <bartsmissaert@blueyonder.co.uk> 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.
    >
    > Thanks for any advice.
    >
    > RBS
    >
     
  4. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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
    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" <wijnen.nospam.please@online.no> 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" <bartsmissaert@blueyonder.co.uk> 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.
    >>
    >> Thanks for any advice.
    >>
    >> RBS
    >>

    >
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You could always consider using MSSQL (MSDE) for the database
    PIVOTING The Data is childs play in Access (there's a Query wizard that
    gives you the gist)
    you would probably (maybe) still use Access as a middle layer (to store
    login info for the SQL Tables)

    HTH


    Pieter

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:OOBm$w%23eGHA.5104@TK2MSFTNGP04.phx.gbl...
    > 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
    > 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" <wijnen.nospam.please@online.no> 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" <bartsmissaert@blueyonder.co.uk> 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.
    >>>
    >>> Thanks for any advice.
    >>>
    >>> RBS
    >>>

    >>
    >>

    >
     
  6. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    > You could always consider using MSSQL (MSDE)

    Could do, but installation of my app would be more difficult. Nearly all
    customers
    have Access installed already. Also as the DB will only be used on the local
    PC for
    one user only, not sure what the benefit would be.

    Didn't know you could do a Pivot in Access. That sounds promising then.
    Thanks for the tip.

    RBS


    "Pieter Wijnen" <wijnen.nospam.please@online.no> wrote in message
    news:ebSIdA$eGHA.764@TK2MSFTNGP05.phx.gbl...
    > You could always consider using MSSQL (MSDE) for the database
    > PIVOTING The Data is childs play in Access (there's a Query wizard that
    > gives you the gist)
    > you would probably (maybe) still use Access as a middle layer (to store
    > login info for the SQL Tables)
    >
    > HTH
    >
    >
    > Pieter
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:OOBm$w%23eGHA.5104@TK2MSFTNGP04.phx.gbl...
    >> 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
    >> 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" <wijnen.nospam.please@online.no> 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" <bartsmissaert@blueyonder.co.uk> 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.
    >>>>
    >>>> Thanks for any advice.
    >>>>
    >>>> RBS
    >>>>
    >>>
    >>>

    >>

    >
    >
     

Share This Page