Standard or Advanced?

Can you print from within Sage 100 on your workstation?  (That proves the SOTAMAS90 user DSN is functional).

An Excel connection string is auto-created when you use MS Query (Data - From Other Sources...).

Advanced

Yes, just printed the Open Sales Order Report

There are many reasons I don't use MS Query.  If you want to have columns with formulas you have to transfer the data to another table.  I'm an old school control freak who is used to using just writing SQL to get what I want.  I'm not a fan of the user interface, which is time consuming because it is written to facilitate obtaining data when you don't really know how to write SQL statement.   Refreshing the queries from VBA is less than desirable. ...

I'm not just writing reports; I'm using the data and automating functions.

Normally I suggest creating a silent DSN if you are going to extract data on a regular basis. Also is the files are being used, say in updating data entry files the files you are trying to access are locked making extracting not possible or delayed.

Have you ever considered pulling the data with Access.  Many times it is easier to build a quick and easy Access database to pull the data and create datasets to your liking.

Can you explain the pros and cons of a silent DSN and the safety concerns in a manner our IT dept & CFO can understand?

They were a little nervous when I started using the DSN on my own.  While my credentials kept me out of Payroll in the Sage application, the way the DSN was set up didn't keep me out via Excel.  Our CFO figured out how to put the security on the DSN,  They've never had anyone who could do what I'm doing with Excel.

I found instructions here:  https://support.na.sage.com/selfservice/viewContent.do?externalId=19495&sliceId=1

I don't believe it's a timing issue because it never fails anywhere but my PC.  And it doesn't matter if I'm querying Open SOs, POs, Item Master, etc when it's in failure mode it will not return results.

Are you sure you don't have multiple Data Sources on your PC?  If you have a User DSN and a System DSN pointed to different sources, maybe it is sometimes using one and sometimes the other.  

In the ODBC Data Source Administrator

on the System DSN tab there is Nothing

on the User DSN tab there are (3):  MAS, Access and Excel

MS Query would just be to make sure you have a working connection string, and that there are no issues with the SOTAMAS90 connection / paths.

Another possible issue is the 32-bit ODBC driver.  If you are running 64-bit Excel, you need to install the 64-bit ODBC drivers for Providex.

There is no problem with the connection string.

I am running 64 bit Excel and have the correct ODBC driver installed.

The problem is intermittent not persistent.

The connection never fails, the query returns ZERO results

Silent DSN's hard code the company code / username / password, for the workstation... making it easier to run queries (no prompt to authenticate).  If you use a login without access to the sensitive data, you're fine.