SQL From Access Not Working

[thegreatone2112@yahoo.com]

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)="BILLTO"))
ORDER BY PROD_CUST.CUST_NAME;

[Tom Ogilvy]

See example 4 in this article:

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

--
Regards,
Tom Ogilvy


"thegreatone2112@yahoo.com" 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)="BILLTO"))
> ORDER BY PROD_CUST.CUST_NAME;
>
>