Does the error occur on other tables using the x64 driver?

C:\Windows\System32\odbcad32.exe

Select the SOTMAS90 driver from the User DSN Tab

Go to the DEBUG tab and test your connection.

If the connection is successful, go to the Options Tab and select Null Date, click apply and rerun your query.

If the query is successful, their are bad dates in the file.

John Nichols

Sage 

The connection tested successful and I checked to "on" the NULL Date setting on the options tab and retested the connection as successful, but it failed with the same error when I refreshed the data. I also made the same change (i.e. EnforceNULLDate=1; ) in the properties of the query's connection. EnforceNULLDate was not specified, so I added it, saved the change, closed Excel and re-opened/tested but it failed again with the same error. I have also tried with and without enabling the C/S ODBC Driver. I also tried running the CheckDate utility, but it reported nothing. It's really a shame to have the client re-install the 32-Bit version of Office 365.

Perhaps try removing columns from your query, one by one, to narrow down the issue?

Good idea. I'll try, but the oddest thing is that the same query (there are several queries that fail - using multiple workstations and multiple user ids - all against large history tables) that fails will occasionally work. It has yet to fail with 32-Bit Excel.

Hi.

"will occasionally work"  .  Can you post your connection string (Obscure the server name) and the SQL statement?  The next time it fails paste the connection string "Directory" value into the workstation's file explorer path.

As Kevin suggested restrict the SQL statement to fewer columns or just the key value of the table.

It's either the connection string or the SQL statement. There is no need to move them back to x86 Office.   

Failing Connection String:

DSN=SOTAMAS90;Description=MAS 90 4.0 ODBC Driver;Directory=..\MAS90;Prefix=..\MAS90\SY\, ..\MAS90\==\;ViewDLL=..\MAS90\HOME;LogFile=\PVXODBC.LOG;RemotePVKIOHost=SAGE_SRV;RemotePVKIOPort=20222;CacheSize=4;DirtyReads=1;BurstMode=1;StripTrailingSpaces=1;SERVER=NotTheServer

1. If I removed the C/S ODBC parameters:
   RemotePVKIOHost=SAGE_SRV;RemotePVKIOPort=20222;
   from the connection string it works with 64-Bit.
2. Eliminating fields in order to isolate a problem field value proved inconclusive
   1. If I eliminated fields from the query down to just 'ReferenceDate' it sill failed
   2. If I replaced 'ReferenceDate' with  'TransactionDate' it worked - however...
   3. If I put 'ReferenceDate' back into the SQL statement it still worked.
   4. If I used 'TransactionDate' instead of 'ReferenceDate' in the original query (before eliminating14 other fields) and using the C/S ODBC parameters, the query still failed.
   5. Thus I concluded that 'ReferenceDate' was not the issue. I had also written a Crystal Report and a MS Access (32-Bit) query that grouped all the ReferenceDate field values in the table and no unusual dates were found.
3. What I learned:
   1. 32-Bit Excel works with the Sage 100 ODBC (32/64Bit) driver with C/S ODBC enabled or disabled against large history tables on version 2019 (6.10.5.0)
   2. 64-Bit Excel with the Sage 100 ODBC (32/64-Bit) driver has an 'Internal Error' problem with C/S ODBC enabled in the connection string when run against large history tables on version 2019 (versions tested: 6.10.0 & 6.10.5.0, both failed with the same error)
   3. My earlier observation that queries would "occasionally work" is explained by how connection string is used. Excel uses the connection string in the query instead of the current connection string in the ODBC control panel DSN (e.g. SOTAMAS90). Thus if a query is created while the C/S ODBC is enabled, the connection string in the Excel file will retain those settings even after the C/S ODBC is disabled for the current (or last) Sage 100 logged-in user in the Windows session. One might think that C/S ODBC is turned off after disabling it in User Maintenance because the current DSN doesn't show "Server" tab parameters although it remains active because it is triggered by the "Server" parameters in the connection string stored in the Excel file if the C/S ODBC Driver service is running.

Great detective work! Better than reading a mystery story.

Did you try restarting the CS ODBC service to see if that helps?

Did you try restarting the CS ODBC service to see if that helps?