Sage 100

 View Only
Back to discussions
Expand all | Collapse all

ODBC Queries in Excel and Access causes crash to desktop

  • 1.  ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-19-2020 22:09
    I received two calls today from different clients with similar problems.

    One with numerous Excel files using ODBC queries to Sage 2018 Std, the other with an Access "application" using linked tables (as far as I can tell, I didn't create it) on Sage 2019 Std. Both crash to the desktop immediately after issuing a refresh data command with no errors. 

    Tests are successful through the ODBC Data Sources, for both Sage 32 and 64 bit sources. Tests on the working Access computer after enabling Legacy Connection Wizards in Excel with a new query fail with "tables cannot be loaded". Tests on the non working computer produce the same crash to desktop when trying to setup the connection.

    The problems is isolated to specific computers as there are other users in both companies for which the the queries still work. Same versions on Excel/Access and Windows 10 (2004). I suspect there was a Windows update that changed some common component as nothing else has changed with respect to the Excel sheets or the Access database.

    Anyone else having sudden ODBC issues?

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------


  • 2.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 08:25
    What happens when you create a new query in Excel?

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



  • 3.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 10:48
    @Jeff Schwenk These were new queries in Excel when I said:

    "Tests on the working Access computer after enabling Legacy Connection Wizards in Excel with a new query fail with "tables cannot be loaded". Tests on the non working computer produce the same crash to desktop when trying to setup the connection."
    ​

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------

    Original Message


  • 4.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 10:59
    I have had issues w 64bit. Are the 2 problem machines 64bit office while working ones have 32?

    ------------------------------
    Eric Lunceford
    First Mate Business Solutions
    Oklahoma City, OK
    877-880-8960
    https://www.firstmatellc.com/
    ------------------------------



  • 5.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 12:34
    @Eric Lunceford no, at client #1 both are 32bit, at client #2 it is a mix of both​​​​. I even tried uninstalling Office and reinstalling 32 bit with no success. Keep in mind this has been working for years and has just stopped. I still believe it must be related to Windows Updates, as that is the only moving part that I can see.

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------

    Original Message


  • 6.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 12:40
    My first thought was bad date values causing the crashes... worth a check:
    CheckDate Sage Utility.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 7.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 13:21
    @Kevin Moyes it works fine on other computers. I am not even able to create an ODBC query, it never even gets to the point where I can select a table.

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------

    Original Message


  • 8.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 13:36
    Maybe try manually creating a System DSN (32 or 64 bit depending on your application) and see if you get the same behavior.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 9.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 14:05
    This issue is happening to me right now on a TS install.

    This may be an AV setting.  Trying to locate the thread that @Todd Martin posted.  Sounds very familiar now. ​

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

    Original Message


  • 10.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 14:31
    @Doug Higgs I can try that and report back. @Jeff Schwenk both of these clients have Trend Micro. It is installed on all workstations with the same settings, including the ones that are working., but it's worth a try to disable it and test.

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------

    Original Message


  • 11.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 14:39
    Here is the link (finally) of what @Todd Martin ​ posted.

    https://community.90minds.com/communities/community-home/digestviewer/viewthread?MessageKey=e287fb40-5bfd-4320-aed5-779dbadffd7e&CommunityKey=4593d843-4557-43ff-ba4d-6240681ed696&tab=digestviewer#bme287fb40-5bfd-4320-aed5-779dbadffd7e


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

    Original Message


  • 12.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 15:10
    @Jeff Schwenk Oh that sounds exactly like what it could be. Logging in right now to see if that setting is enabled.​..

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------

    Original Message


  • 13.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 15:18
    Such a good memory you have @Jeff Schwenk! Hopefully that will fix you @Greg Stiles.​​

    ------------------------------
    Todd Martin - President
    MBA Business Software (https://www.mbabsi.com)
    ------------------------------

    Original Message


  • 14.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 15:43
    @Jeff Schwenk & @Todd Martin DING! DING! DING! That was it. Thanks so much. Changed that setting and it is working at both clients. Changing same on all other clients. Must gave been enabled after the latest updates.

    Thanks again. Case closed!​​​

    ------------------------------
    Greg Stiles
    S & W Microsystems
    Torrance CA
    310.787.1010
    ------------------------------

    Original Message


  • 15.  RE: ODBC Queries in Excel and Access causes crash to desktop

    Posted 10-20-2020 17:21
    Good to hear.  The memory was like an itch.  just kept getting worse.  The hard part was coming up with the search word that works.  I probably spent 40 minutes digging in HL (do you know "schwenk" had 18,000+ hits).  Tried search Outlook as I knew I had sent it to a couple clients.  Glad it worked out for you @Greg Stiles.  But hats off to @Todd Martin for posting the original solution!!!!!
    ​​

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

    Original Message


Related Content