Create an Access database. Create SQL Specific Pass Through Queries, one for each table. Base your queries on the Access database. It will work and will be faster than doing an ODBC connection to SOTAMAS90 strange as that may seem.  

Try this query:

SELECT SO_SalesOrderHeader.*

FROM   SO_SalesOrderHeader, AR_Customer

WHERE  SO_SalesOrderHeader.ARDivisionNo=AR_Customer.ARDivisionNo AND SO_SalesOrderHeader.CustomerNo=AR_Customer.CustomerNo

Or as BigLouie said, you won't have nearly the same issues with SQL if you link through Access.

PVX-ODBC4x.pdf

Hi Try the statement below.

SELECT * FROM {oj SO_SalesOrderHeader JOIN AR_Customer ON AR_Customer.CustomerNo = SO_SalesOrderHeader.CustomerNo}

You can't use an x64 version of SQL with a 32 (x86) ODBC driver.  There is a x64 bit driver available with Sage 100 2013.

I've attached the ODBC doc and a link to supported syntax below.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms711813(v=vs.85).aspx

Thanks

John Nichols

Sage

Thank you so much for all of your feedback! Typically I would go the Access route, however due to the way my .NET application is constructed, I don't feel I will be able to use that approach without drastically reconstructing my application. I really wanted to replace the queries I had.

I tried the queries from kdb and jcnichols, and both worked great. I'm curious to learn what the "{oj" in the query jcnichols provided is. Would you be able to explain that to me? I also noticed it was not as simple as adding that to the beginning of a more complex query and it will still work. For instance, if I add another JOIN to the query (joined the SO_SalesOrderDetail), it does not work:

SELECT * FROM {oj SO_SalesOrderHeader JOIN AR_Customer ON AR_Customer.CustomerNo = SO_SalesOrderHeader.CustomerNo JOIN SO_SalesOrderDetail ON SO_SalesOrderDetail.SalesOrderNo = SO_SalesOrderHeader.SalesOrderNo}

{oj} stands for OuterJoin

Sandy,

What I find helpful is to build your queries in Crystal Reports and then view the SQL Statement. It will build ProvideX compatible queries for both simple and complex requests. Crystal Reports should come with Sage 100 if you don't already have it installed.

kdb