Access 2016 ODBC / Sage Issue

SOLVED

Hello.  We are having a bizarre issue with Microsoft Office 2016 and an ODBC connection to Sage 100 Advanced 2018 ERP.  We use a system DSN to connect to Sage from Access to run queries and reports not available in Sage and have been doing this for years.  What is happening is as soon as the ODBC connection is made, the Access (or Excel) program shuts down completely.  This happens on 32- or 64-bit Access, 32- or 64-bit ODBC Sage drivers, and Windows 10 or Windows 7 workstations.  Here is a timeline of events and what I have attempted to do to correct the issue:

02/08/2019 and prior: Access worked to connect via ODBC.

02/10/2019: I performed a Trend Micro WFBS Advanced hotfix update Version 10 (Patch 1494), Windows 2012R2 server updates, and rebooted the server.

02/11/2019:  Access worked to connect via ODBC.

02/12/2019:  Several Windows and Office updates were deployed to all workstations.  Office 2016 is set to update automatically and does whenever there is a new revision.  Version of Access/Office: 1901 (Build 11231.20130).

02/13/2019: User reported Access crashes when ODBC connection is made to Sage.  He uses 32-bit Office 2016 Pro with the 32-bit Sage ODBC driver on 64-bit Windows 10 Pro.  Access crashes immediately when the ODBC connection is made.

02/13/2019: I tested the connection on my PC and had the same issue.  I am running 64-bit Office 2016 Pro with the 64-bit Sage ODBC driver on a 64-bit Windows 10 Pro.  Access crashes immediately when the ODBC connection is made.

02/13/2019: I tested the connection on a 32-bit Office 2016 Pro with 32-bit Sage ODBC driver on a 64-bit Windows 7 Pro workstation.  Access crashes immediately when the ODBC connection is made.

02/13/2019: I performed a rollback of the Office 2016 version to the January 14th release (when I know it worked), Version 1812 (Build 11126.20266).  Issue still occurs.

02/13/2019:  I performed a system restore on Windows 10 Pro to see if it was a Windows update, but as soon as system reboots, the uninstalled update re-installs.

02/13/2019:  I uninstalled and re-installed the Sage workstation setup and ODBC drivers. Issue still occurs.  I uninstalled and re-installed Office 2016 Pro.  Issue still occurs.  I called Sage and they had me re-do the troubleshooting I had already done.  They said it is out of their hands because Microsoft is a 3rd party product.  I called Microsoft, they said I had done what they would recommend and because Sage is a 3rd party product, they couldn’t help me any further. 

02/17/2019:  I installed the next TM patch, 1516.  Installed latest Windows 2012R2 updates.  Rebooted server.

02/18/2019 - Current:  Issues still occurs and I am ready to pull out my hair.  We are still able to manually export table data from Sage into Access, but it is cumbersome.

Does anyone have any ideas? 

  • 0

    With Advanced, you have CS-ODBC as an option, which might help.  You don't say whether CS- ODBC is being used... but if it is, make sure your DSN is set up with the correct servername / port#.

    Try SOTAMAS90 instead of your system DSN.

    Check the Windows Event Viewer for application crash details.

    Make sure your DSN's "test" successfully.

  • 0 in reply to Kevin M

    Thank you, Kevin.  Yes, the connection succeeds.  We do not use the CS version of ODBC, but tried it to no avail.  I forgot to mention that in my post.

  • +1
    verified answer

    I heard a report of an Excel 2016 / 2013 crash occurring when Sage 100 ODBC connection was made. This happened after recent Trend Micro WFBS update after working successfully for over a year. It was fixed by disabling this setting in TM:

    "Terminate programs that exhibit abnormal behavior associated with exploit attacks" under Devices > Desktop > Configure Policy > Behavior Monitoring > Ransomeware Protection > Anti-Exploit Protection

    Sounds like this could be applicable to you as well.

    Also in Access, not sure if applicable to you but I've had to go to File > Options > Trust Center > Trusted Locations and in my case add a UNC path after checking the box for Allow Trusted Locations of My Network.

  • 0 in reply to Alnoor

    Hello, Alnoor!

    You are my hero!  It was the Anti-Exploit option that was crashing my databases!  Thank you, thank you, thank you!!!!  My network locations were already added to Trusted Locations, but good old TM was the culprit.