=========
QUESTION
———
When I write a regular query in Microsoft Access against a table in an Oracle Database, and then convert it to a SQL Pass-Through query, it will not run, and shows errors like the following: 
—
ODBC–call failed.

[Oracle][ODBC][Ora]ORA-00903: invalid table name
(#903)

OK     Help
—

—
ODBC–call failed.

[Oracle][ODBC][Ora]ORA-00396: missing expression
(#936)

OK    Help
—

 

=========
ANSWER
———
When Microsoft Access converts a standard query to SQL Pass-Through, it generally does not take into account what type of database it will be going against and does  not update the SQL Query language accordingly.

To write a SQL Pass-Through query against an Oracle database, you will need to update your SQL to Oracle friendly SQL

 

=========
EXAMPLES
———
If you were converting the following Query to a SQL Pass-Through query:
—
SELECT XBRADMIN_POS_SKU_TAB.STORENUM AS Store, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY, XBRADMIN_POS_SKU_TAB.LINENUM, Int([CASHIERNUM]) AS SalesID, XBRADMIN_POS_SKU_TAB.ITEM, XBRADMIN_POS_SKU_TAB.MERCH_DEPT, XBRADMIN_POS_SKU_TAB.MERCH_CLASS INTO 2010T_POSSkuTab
FROM XBRADMIN_POS_SKU_TAB
WHERE (((XBRADMIN_POS_SKU_TAB.STORENUM)<3000) AND ((XBRADMIN_POS_SKU_TAB.TRANSDATE) Between #7/31/2011# And #8/6/2011#) AND ((XBRADMIN_POS_SKU_TAB.ORGID)=1) AND ((XBRADMIN_POS_SKU_TAB.DIVISION)=1) AND ((XBRADMIN_POS_SKU_TAB.TRANSSTAT)="COMPLETE") AND ((XBRADMIN_POS_SKU_TAB.TRANSTYPE)<>"RETURN") AND ((XBRADMIN_POS_SKU_TAB.TRAINING_FLAG)="N") AND ((XBRADMIN_POS_SKU_TAB.VOID_CODE)=0))
ORDER BY XBRADMIN_POS_SKU_TAB.STORENUM, XBRADMIN_POS_SKU_TAB.TRANSDATE, XBRADMIN_POS_SKU_TAB.TRANSNUM, XBRADMIN_POS_SKU_TAB.REGNUM, XBRADMIN_POS_SKU_TAB.QUANTITY;
—

You would first need to update the fields (dates, remove double quotes on where criteria, etc…) to look like this:
—
SELECT STORENUM AS Store, TRANSDATE, TRANSNUM, REGNUM, QUANTITY, LINENUM, CASHIERNUM AS SalesID, ITEM, MERCH_DEPT, MERCH_CLASS
FROM XBRADMIN.POS_SKU_TAB
WHERE (((STORENUM)<3000) AND ((TRANSDATE) Between '31-jul-2011' And '6-aug-2011') AND ((ORGID)=1) AND ((DIVISION)=1) AND ((TRANSSTAT)='COMPLETE') AND ((TRANSTYPE)<>'RETURN') AND ((TRAINING_FLAG)='N') AND ((VOID_CODE)=0))
ORDER BY STORENUM, TRANSDATE, TRANSNUM, REGNUM, QUANTITY;

 

=========
APPLIES TO / KEY WORDS
———
Microsoft Access
SQL Pass Through
Oracle
ODBC

 

=========
REF
———
http://www.dutchvalley.net/whitepaper/Creating%20a%20Pass.pdf

 

—
http://www.anysitesupport.com/access-sql-pass-through-query-against-oracle/ 
http://anySiteHosting.com