Sage 100

 View Only

  • 1.  Excel Freezes When Refreshing or Updating a Query

    Posted 04-10-2020 11:34
    I am working with a customer that is experiencing some weird things in Microsoft Excel Query.  I ran into this issue with a different customer before, but never found a solution.

    The query is pointed to the SOTAMAS90 ODBC connection and is pulling in 3 different tables:  AR_Customer, AR_InvoiceHistoryHeader, and AR_InvoiceHistoryDetail.  When the user tries to refresh the report, it goes into the not responding phase (understood that it will take some time), and eventually it just fails to run.  When I try to edit the query in Microsoft Query, it freezes up there and I have to kill the task. If I create a new query and only use one table, it works fine.  But as soon as I add a 2nd table, it freezes up and i can't do anything.

    Does anyone have experience with Excel/Microsoft Query to know what's going on with it and a way to fix this?  I believe the customer is on Excel 2016.

    ------------------------------
    Chris Love
    Accounting Systems, Inc. (ASI)
    ------------------------------


  • 2.  RE: Excel Freezes When Refreshing or Updating a Query

    Posted 04-10-2020 11:43
    Excel queries have initial high failure rate when pulling from more than two tables.  Usually, editing the query and adjusting the joins does the trick.  Based on the three files used, I would pull the history detail first, header next and customer last.  Verify your joins are correct.  Since you see the sotamas driver, it isn't a 64 bit driver issue.

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------



  • 3.  RE: Excel Freezes When Refreshing or Updating a Query

    Posted 04-10-2020 15:08
    Jeff,

    Thanks for the feedback, this was one of 2 things that fixed the report for me.  Working with one of our developers, we also determined that the filtering they were using in the spreadsheet on the AR_InvoiceHistoryHeader.InvoiceDate field was part of the problem as well.  You can use Invoice Date as part of the key for the invoice history header table, but it also requires the data to be sorted in Descending order based on the Inv. Date field.  Once we changed the order of the linking and put in the Descending Sort on the Invoice Date field, the report ran in about 5 minutes.

    Chris

    ------------------------------
    Chris Love
    Accounting Systems, Inc. (ASI)
    ------------------------------

    Original Message


  • 4.  RE: Excel Freezes When Refreshing or Updating a Query

    Posted 04-13-2020 10:32
    With Excel 2016, the preferred query tool is Microsoft's Power Query. It has a much better interface than MS Query and it's stores the transformation of the data in steps.

    ------------------------------
    Joe Uhl
    Eide Bailly, Inc.
    ------------------------------

    Original Message


  • 5.  RE: Excel Freezes When Refreshing or Updating a Query

    Posted 04-10-2020 14:06
    I would also try from another PC to isolate whether it's the Driver, Excel or a Sage100 data file. Maybe try another Company and the same data files.

    ------------------------------
    Kenny Daniel
    Technoclarity, Inc.
    ------------------------------



  • 6.  RE: Excel Freezes When Refreshing or Updating a Query

    Posted 04-10-2020 17:15
    Good to hear.  And just in time for Friday PM virtual bar social!!  Enjoy the weekend.

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------

    Original Message


  • 7.  RE: Excel Freezes When Refreshing or Updating a Query

    Posted 04-13-2020 17:16
    Edited by Alnoor Cassim 04-13-2020 19:04

    In case helpful, to give some backdrop, customer had an existing working pivot table in Excel, not using Power Pivot nor Power Query. Either of the latter 2 would have been optimal but still the sort of mystical thing I've seen @Robert Wood, @Dan Burleson, @Jeff Schwenk do with pivot tables (and dozens more of you too).  2 weeks ago it was working fine w/o any tweaks.  Same freeze occurred with Crystal on same tables. Appeared Webroot AV exclusions were not in effect but we didn't have access to change them.

    Invoice Date was in the query noticed KINVOICEDATE index / alternate key in File Layouts . InvoiceDate(d) is the 1st column which means if you sort or group in descending order, it will add an ORDER BY InvoiceDate DESC clause to the SQL stmt and this gave us a fast result b/c it was a keyed/indexed search.

    Normally sorting this way may not be how you want a report to show in Crystal. But we knew pivot table was further re-grouping the data once it had initial data. Solution was to add the ORDER BY in Excel SQL stmt for the fast initial result and then pivot table re-grouped it locally.

    ** In hindsight, I could have done ORDER BY InvoiceNo,HeaderSeqNo the KPRIMARY columns to run it even faster. I've even mentioned in past posts to sort by KPRIMARY columns for faster Access SELECT queries on history tables. The point is with ProvideX ODBC, sorting by a key column gives you a faster indexed search. 



    ------------------------------
    Alnoor Cassim

    Accounting Systems, Inc. (ASI)
    Email: alnoor@asifocus.com
    Orange County, CA
    ------------------------------

    Original Message


Related Content