ODBC error on Access 2003

[SpringK]

I am using Access 2003 as the front end and thru ODBC connecting to a SQLBase 8.1 database. My code runs fine on Access 2000 or XP. I get an ODBC error (obtuse error coding) with Access 2003 only.

One of the queries that I am having problems with is -

INSERT INTO tblTmpMonthSales ( PostDate, BillToState, BillToCntry, ShipToState, GLAcct, InvAmt, SlsTaxGrp, CustAddState )
SELECT SYSADM_RECEIVABLE.POSTING_DATE, SYSADM_CUSTOMER.BILL_TO_STATE, SYSADM_CUSTOMER.COUNTRY, SYSADM_SHIPTO_ADDRESS.STATE AS SHIP_TO_STATE, SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID, SYSADM_RECEIVABLE_LINE.AMOUNT, SYSADM_CUSTOMER.DEF_SLS_TAX_GRP_ID, SYSADM_CUSTOMER.STATE
FROM (SYSADM_SHIPTO_ADDRESS RIGHT JOIN ((SYSADM_RECEIVABLE INNER JOIN SYSADM_CUSTOMER ON SYSADM_RECEIVABLE.CUSTOMER_ID = SYSADM_CUSTOMER.ID) LEFT JOIN SYSADM_SHIPPER ON SYSADM_RECEIVABLE.INVOICE_ID = SYSADM_SHIPPER.INVOICE_ID) ON SYSADM_SHIPTO_ADDRESS.ADDR_NO = SYSADM_SHIPPER.SHIP_TO_ADDR_NO) INNER JOIN SYSADM_RECEIVABLE_LINE ON SYSADM_RECEIVABLE.INVOICE_ID = SYSADM_RECEIVABLE_LINE.INVOICE_ID
WHERE (((SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"25000-001" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-100" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-500" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"49000-009" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-210" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-250" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-230" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-300" And (SYSADM_RECEIVABLE_LINE.GL_ACCOUNT_ID)<>"74100-310") AND ((DatePart("yyyy",[POSTING_DATE]))=[Forms]![frmPrintReports]![txtYear]))
ORDER BY DatePart("yyyy",[POSTING_DATE]);

The issue seems to be the left join between SYSADM_RECEIVABLE and SYSADM_SHIPPER. I am having multiple problems with left joins on all my queries.

The error that I get is just ODBC - Call Failed.

Any help would be appreciated.

Thanks in advance.

Regards

Karl

 

[rohalyla]

It sounds like you may have a problem with the ODBC driver. When you receive an ODBC call failed, it means it cannot connect to the db, so I don't believe the problem is with your query. Try updating the ODBC driver or viewing the SQLBase documentation to see if it is a compatible version with Access 2003.

 

[SpringK]

Thanks. If that is the problem, it will make it a lot easier.

Karl