Hi, Version v4.40.0004 of the ODBC driver is current. Can you post your SQL query and I'll take a look.

Thanks 

John Nichols

Sage

John, I don't think the query is the problem.  If I run the query with Excel 2013 it works fine.  If I run it with Excel 365 Enterprise 64 bit is when I get the error.  Here's the query:

SELECT AR_InvoiceHistoryDetail.InvoiceNo, AR_InvoiceHistoryDetail.ItemCode, AR_InvoiceHistoryDetail.ItemType, AR_InvoiceHistoryDetail.ItemCodeDesc, AR_InvoiceHistoryDetail.QuantityShipped, AR_InvoiceHistoryDetail.UnitPrice, AR_InvoiceHistoryDetail.UnitCost, AR_InvoiceHistoryDetail.ExtensionAmt, AR_InvoiceHistoryHeader.CustomerNo, AR_InvoiceHistoryHeader.BillToName, AR_InvoiceHistoryHeader.InvoiceType, AR_InvoiceHistoryHeader.InvoiceDate, AR_InvoiceHistoryHeader.SalespersonNo
FROM AR_InvoiceHistoryDetail AR_InvoiceHistoryDetail, AR_InvoiceHistoryHeader AR_InvoiceHistoryHeader
WHERE AR_InvoiceHistoryDetail.InvoiceNo = AR_InvoiceHistoryHeader.InvoiceNo AND AR_InvoiceHistoryDetail.HeaderSeqNo = AR_InvoiceHistoryHeader.HeaderSeqNo AND ((AR_InvoiceHistoryDetail.ItemType='1') AND (AR_InvoiceHistoryHeader.InvoiceDate>=? And AR_InvoiceHistoryHeader.InvoiceDate<=?))
ORDER BY AR_InvoiceHistoryHeader.CustomerNo, AR_InvoiceHistoryDetail.ItemCode, AR_InvoiceHistoryHeader.InvoiceDate

If you take out the Order By CustomerNo it works on Excel 365.  Thanks for taking a look!

If only the CustomerNo field causes the problem, I'd look for bad data in that field.

Kevin,

It's not the only field that causes the problem although it seems to be only fields that I use to ORDER BY.  This occurs in multiple tables with multiple ORDER BY fields.  However, these exact queries on the same data using Excel 2013 has no problems.  I've rebuild some of the files and same error with Excel 365.

Thanks!  Mark

I have a SQL linked server set up using the 64-bit ODBC driver, and order by InvoiceNo works, but not CustomerNo.  Odd, but this confirms the issue is not specific to Excel.  (I have no further insights).

Thanks, Kevin!  I'll pass this along to Sage.  I have a feeling they have some issues with their 64 bit ODBC driver.  Appreciate your help!  Mark

If you were not aware, jcnichols above is on the Sage 100 development team.  :-)

Thanks, I was not aware.  LOL Hopefully that will speed up the discovery process!

Opening a case with Sage support is not a bad idea too (if you have not already done that).

John,

FYI, Kevin M was able to duplicate the problem I'm having when he tries to access a SQL database using the Providex 64 bit ODBC driver.  (see below)  I'm beginning to think there may be an issue with that driver not only accessing Excel but SQL databases as well.

Thanks!

I already did that yesterday but they say don't have any clues.  They suggested Sage City.

Kevin - I seem to recall the Path to Views DLL has a 32-bit component. Maybe it's interfering. Maybe you are inclined to test that :-) If you do, I would setup a new Linked Server separate from your current MAS_ABC64. Now if you do this by passing the System DSN name, then prior to this setup the silent connect DSN so Path to Views DLL is blank. But if you set it up by passing the connection string instead, then omit the ViewDLL= part.

Kevin - I seem to recall the Path to Views DLL has a 32-bit component. Maybe it's interfering. Maybe you are inclined to test that :-) If you do, I would setup a new Linked Server separate from your current MAS_ABC64. Now if you do this by passing the System DSN name, then prior to this setup the silent connect DSN so Path to Views DLL is blank. But if you set it up by passing the connection string instead, then omit the ViewDLL= part.

Same error Alnoor.