Hello,
The following SQL statement Access created after I made a querry in
design mode. The Access database is connected to Oracle.

I copied the SQL statement from the Access querry and pasted into
range("A1")

Here's how the Excel connection is being made:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=XXXX;UID=XXXX;PWD=####;SERVER=XXXX; (i removed the
actual names)

For the CommandText I am referencing cell A1, which contains the
following SQL statement.
This returns an error and won't execute.
Any ideas.
Thank you.

Sql From Access

SELECT PROD_ADDR.ADDR_NO, PROD_CUST_ADDR.CUST_NO,
PROD_CUST.USER_CUST_NO, PROD_CUST.CUST_NAME,
PROD_CUST_ADDR_FUNC_CD.CUST_ADDR_FUNC_CD, PROD_ADDR_LN.ADDR_LN_NO,
PROD_ADDR_LN.ADDR_LN_TXT, PROD_ADDR.CITY, PROD_ADDR.STATE_CD,
PROD_ADDR.ZIP
FROM ((PROD_CUST INNER JOIN (PROD_ADDR INNER JOIN PROD_CUST_ADDR ON
PROD_ADDR.ADDR_NO = PROD_CUST_ADDR.ADDR_NO) ON PROD_CUST.CUST_NO =
PROD_CUST_ADDR.CUST_NO) INNER JOIN (PROD_CUST_ADDR_FUNC_CD INNER JOIN
PROD_CUST_ADDR_FUNC_REL ON PROD_CUST_ADDR_FUNC_CD.CUST_ADDR_FUNC_CD =
PROD_CUST_ADDR_FUNC_REL.CUST_ADDR_FUNC_CD) ON (PROD_ADDR.ADDR_NO =
PROD_CUST_ADDR_FUNC_REL.ADDR_NO) AND (PROD_CUST_ADDR.CUST_NO =
PROD_CUST_ADDR_FUNC_REL.CUST_NO)) INNER JOIN PROD_ADDR_LN ON
PROD_ADDR.ADDR_NO = PROD_ADDR_LN.ADDR_NO
WHERE (((PROD_CUST_ADDR_FUNC_CD.CUST_ADDR_FUNC_CD)="BILL TO"))
ORDER BY PROD_CUST.CUST_NAME;

See example 4 in this article:

http://support.microsoft.com/kb/105416/en-us

--
Regards,
Tom Ogilvy


" wrote:

Hello,
The following SQL statement Access created after I made a querry in
design mode. The Access database is connected to Oracle.

I copied the SQL statement from the Access querry and pasted into
range("A1")

Here's how the Excel connection is being made:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=XXXX;UID=XXXX;PWD=####;SERVER=XXXX; (i removed the
actual names)

For the CommandText I am referencing cell A1, which contains the
following SQL statement.
This returns an error and won't execute.
Any ideas.
Thank you.

Sql From Access

SELECT PROD_ADDR.ADDR_NO, PROD_CUST_ADDR.CUST_NO,
PROD_CUST.USER_CUST_NO, PROD_CUST.CUST_NAME,
PROD_CUST_ADDR_FUNC_CD.CUST_ADDR_FUNC_CD, PROD_ADDR_LN.ADDR_LN_NO,
PROD_ADDR_LN.ADDR_LN_TXT, PROD_ADDR.CITY, PROD_ADDR.STATE_CD,
PROD_ADDR.ZIP
FROM ((PROD_CUST INNER JOIN (PROD_ADDR INNER JOIN PROD_CUST_ADDR ON
PROD_ADDR.ADDR_NO = PROD_CUST_ADDR.ADDR_NO) ON PROD_CUST.CUST_NO =
PROD_CUST_ADDR.CUST_NO) INNER JOIN (PROD_CUST_ADDR_FUNC_CD INNER JOIN
PROD_CUST_ADDR_FUNC_REL ON PROD_CUST_ADDR_FUNC_CD.CUST_ADDR_FUNC_CD =
PROD_CUST_ADDR_FUNC_REL.CUST_ADDR_FUNC_CD) ON (PROD_ADDR.ADDR_NO =
PROD_CUST_ADDR_FUNC_REL.ADDR_NO) AND (PROD_CUST_ADDR.CUST_NO =
PROD_CUST_ADDR_FUNC_REL.CUST_NO)) INNER JOIN PROD_ADDR_LN ON
PROD_ADDR.ADDR_NO = PROD_ADDR_LN.ADDR_NO
WHERE (((PROD_CUST_ADDR_FUNC_CD.CUST_ADDR_FUNC_CD)="BILL TO"))
ORDER BY PROD_CUST.CUST_NAME;