Hi there,

I use Excel to run a query against Access, which has its tables linked
to an Oracle backend. The objective is to submit the query from Excel
to Access and then have the data returned in an ADO recordset object.
Then I use the CopyFromRecordset method to paste into Excel's
spreadsheet.

The query, when it is run in Access will work, but when used in Excel
with an ADO connection to Access, it fails.

Here is my SQL. Can anyone determine why it would fail when run in
Excel?

I would really appreciate any feedback someone can offer so I can
understand why it's not working. At the moment, I'm clueless.

"SELECT tbl8.CLIENT_NAME AS [CLIENT PROG], " & _
"tbl3.PROGRAM_VERSION_ID AS PROG_VER, " & _
"tbl6.MODULE_ID AS MOD_ID, tbl4.VARIATION AS VAR, " & _
"tbl4.DESCRIPTION AS VER, tbl3.VERSION_ID AS VER_ID, " & _
"tbl7.MODULE_NAME AS MOD_NAME, " & _
"Max(tbl6.ATTEMPT_DATE) AS LAST_ATTEMPT, " & _
"Max(tbl6.PASSED_IND) AS PASSED, tbl2.ENABLED_IND AS ENABLED, " & _
"tbl6.SHOPPER_ID AS SID, tbl1.EDATE AS HIRE_DATE, tbl1.NAME, " & _
"tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, tbl1.HPHONE, tbl1.WPHONE, " & _
"tbl1.CALLWORK, tbl1.EMAIL " & _

"FROM (EMP AS tbl1 INNER JOIN " & _
"(CERT AS tbl2 RIGHT JOIN " & _
"((((PROG_VER AS tbl3 INNER JOIN " & _
"VAR_VER AS tbl4 ON " & _
"tbl3.VERSION_ID = tbl4.VERSION_ID) INNER JOIN " & _
"AUD AS tbl5 ON tbl4.AUDIT_ID = tbl5.AUDIT_ID) " & _
"INNER JOIN ATTEMPTS AS tbl6 ON " & _
"tbl3.PROGRAM_VERSION_ID = tbl6.PROGRAM_VERSION_ID) INNER JOIN " & _
"MOD AS tbl7 ON tbl6.MODULE_ID = tbl7.MODULE_ID) ON " & _
"(tbl2.SHOPPER_ID = tbl6.SHOPPER_ID) AND " & _
"(tbl2.MODULE_ID = tbl6.MODULE_ID)) ON " & _
"tbl1.SHOPPERID = tbl6.SHOPPER_ID) INNER JOIN " & _
"CUST AS tbl8 ON tbl5.CID = tbl8.CID " & _

"GROUP BY tbl8.CLIENT_NAME, tbl3.PROGRAM_VERSION_ID, " & _
"tbl6.MODULE_ID, tbl4.VARIATION, tbl4.DESCRIPTION, " & _
"tbl3.VERSION_ID, tbl7.MODULE_NAME, tbl6.SHOPPER_ID, tbl1.EDATE,
tbl2.ENABLED_IND, tbl1.NAME, tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, " & _
"tbl1.HPHONE, tbl1.WPHONE, tbl1.CALLWORK, tbl1.EMAIL, tbl5.CID " & _

"HAVING (((Max(tbl6.ATTEMPT_DATE)) Between #" & rDate1 & _
"# And #" & rDate2 + 1 - ((1 / 3600) / 24) & "#) " & _
"AND ((Max(tbl6.PASSED_IND))='Y'))"

What are you using to Excute the code? But it looks like the semi-colon is
missing...

"AND ((Max(tbl6.PASSED_IND))='Y'));
..
"
"cb22" wrote:

Hi there,

I use Excel to run a query against Access, which has its tables linked
to an Oracle backend. The objective is to submit the query from Excel
to Access and then have the data returned in an ADO recordset object.
Then I use the CopyFromRecordset method to paste into Excel's
spreadsheet.

The query, when it is run in Access will work, but when used in Excel
with an ADO connection to Access, it fails.

Here is my SQL. Can anyone determine why it would fail when run in
Excel?

I would really appreciate any feedback someone can offer so I can
understand why it's not working. At the moment, I'm clueless.

"SELECT tbl8.CLIENT_NAME AS [CLIENT PROG], " & _
"tbl3.PROGRAM_VERSION_ID AS PROG_VER, " & _
"tbl6.MODULE_ID AS MOD_ID, tbl4.VARIATION AS VAR, " & _
"tbl4.DESCRIPTION AS VER, tbl3.VERSION_ID AS VER_ID, " & _
"tbl7.MODULE_NAME AS MOD_NAME, " & _
"Max(tbl6.ATTEMPT_DATE) AS LAST_ATTEMPT, " & _
"Max(tbl6.PASSED_IND) AS PASSED, tbl2.ENABLED_IND AS ENABLED, " & _
"tbl6.SHOPPER_ID AS SID, tbl1.EDATE AS HIRE_DATE, tbl1.NAME, " & _
"tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, tbl1.HPHONE, tbl1.WPHONE, " & _
"tbl1.CALLWORK, tbl1.EMAIL " & _

"FROM (EMP AS tbl1 INNER JOIN " & _
"(CERT AS tbl2 RIGHT JOIN " & _
"((((PROG_VER AS tbl3 INNER JOIN " & _
"VAR_VER AS tbl4 ON " & _
"tbl3.VERSION_ID = tbl4.VERSION_ID) INNER JOIN " & _
"AUD AS tbl5 ON tbl4.AUDIT_ID = tbl5.AUDIT_ID) " & _
"INNER JOIN ATTEMPTS AS tbl6 ON " & _
"tbl3.PROGRAM_VERSION_ID = tbl6.PROGRAM_VERSION_ID) INNER JOIN " & _
"MOD AS tbl7 ON tbl6.MODULE_ID = tbl7.MODULE_ID) ON " & _
"(tbl2.SHOPPER_ID = tbl6.SHOPPER_ID) AND " & _
"(tbl2.MODULE_ID = tbl6.MODULE_ID)) ON " & _
"tbl1.SHOPPERID = tbl6.SHOPPER_ID) INNER JOIN " & _
"CUST AS tbl8 ON tbl5.CID = tbl8.CID " & _

"GROUP BY tbl8.CLIENT_NAME, tbl3.PROGRAM_VERSION_ID, " & _
"tbl6.MODULE_ID, tbl4.VARIATION, tbl4.DESCRIPTION, " & _
"tbl3.VERSION_ID, tbl7.MODULE_NAME, tbl6.SHOPPER_ID, tbl1.EDATE,
tbl2.ENABLED_IND, tbl1.NAME, tbl1.CITY, tbl1.PROV, tbl1.COUNTRY, " & _
"tbl1.HPHONE, tbl1.WPHONE, tbl1.CALLWORK, tbl1.EMAIL, tbl5.CID " & _

"HAVING (((Max(tbl6.ATTEMPT_DATE)) Between #" & rDate1 & _
"# And #" & rDate2 + 1 - ((1 / 3600) / 24) & "#) " & _
"AND ((Max(tbl6.PASSED_IND))='Y'))"

The semi-colon isn't required. You can create a query in Access' QBE
and then click on the SQL button and remove the semi-colon and it will
still work.

I ended up revising the SQL and it worked. I think it has to do with
the syntax that must be compatible with Oracle--I'm not definite on
this because I don't know Oracle's SQL syntax.

In any case, I appreciate the comment Steven.

:-)