Why do I get an ODBC connection failed error when using UNION ALL in Access?

Question

I'm writing a SQL query in Access 2010 with tables linked to a Pervasive client by ODBC. When I open the tables, the ODBC connection works fine and shows me the values in the table. The query is a series of queries using UNION ALL to merge them together. Recently I added another UNION ALL query and am getting this error:

If I run each part of the query separately, it runs fine. The query I'm connecting via UNION ALL has the exact same number of fields. In fact, the new query is almost identical except for part of it's where clause. Can someone explain why this isn't working?

Here's the query that doesn't work:

SELECT SUM(PR_INP.Futa_5) + SUM(PR_SUTA.Amt_10) AS AMT, '67360' AS DEPT, CDATE(PaidOn) AS PayDate, 'ER Tax Exp:  FUTA/SUTA - Dept 10, 11, 12' AS Memo FROM PR_INP INNER JOIN PR_SUTA ON PR_SUTA.Loc_No = PR_INP.Loc_No AND PR_SUTA.Div_No = PR_INP.Div_No AND PR_SUTA.Emp_No = PR_INP.Emp_No AND PR_SUTA.Pay_Date = PR_INP.Pay_Date WHERE PR_INP.Loc_No = 2170 AND PR_INP.Div_No = 100 AND PR_INP.Pay_Date = CDATE(PaidOn) AND Def_Dept IN ('010', '011', '012')
UNION ALL
SELECT SUM(PR_INP.Futa_5) + SUM(PR_SUTA.Amt_10) AS AMT, '66360' AS DEPT, CDATE(PaidOn) AS PayDate, 'ER Tax Exp:  FUTA/SUTA - Dept 13' AS Memo FROM PR_INP INNER JOIN PR_SUTA ON PR_SUTA.Loc_No = PR_INP.Loc_No AND PR_SUTA.Div_No = PR_INP.Div_No AND PR_SUTA.Emp_No = PR_INP.Emp_No AND PR_SUTA.Pay_Date = PR_INP.Pay_Date WHERE PR_INP.Loc_No = 2170 AND PR_INP.Div_No = 100 AND PR_INP.Pay_Date = CDATE(PaidOn) AND Def_Dept IN ('013')

If I run them separately, there is no error:

SELECT SUM(PR_INP.Futa_5) + SUM(PR_SUTA.Amt_10) AS AMT, '67360' AS DEPT, CDATE(PaidOn) AS PayDate, 'ER Tax Exp:  FUTA/SUTA - Dept 10, 11, 12' AS Memo     FROM PR_INP INNER JOIN PR_SUTA ON PR_SUTA.Loc_No = PR_INP.Loc_No AND PR_SUTA.Div_No = PR_INP.Div_No AND PR_SUTA.Emp_No = PR_INP.Emp_No AND PR_SUTA.Pay_Date = PR_INP.Pay_Date WHERE PR_INP.Loc_No = 2170 AND PR_INP.Div_No = 100 AND PR_INP.Pay_Date = CDATE(PaidOn) AND Def_Dept IN ('010', '011', '012')

SELECT SUM(PR_INP.Futa_5) + SUM(PR_SUTA.Amt_10) AS AMT, '66360' AS DEPT, CDATE(PaidOn) AS PayDate, 'ER Tax Exp:  FUTA/SUTA - Dept 13' AS Memo FROM PR_INP INNER JOIN PR_SUTA ON PR_SUTA.Loc_No = PR_INP.Loc_No AND PR_SUTA.Div_No = PR_INP.Div_No AND PR_SUTA.Emp_No = PR_INP.Emp_No AND PR_SUTA.Pay_Date = PR_INP.Pay_Date WHERE PR_INP.Loc_No = 2170 AND PR_INP.Div_No = 100 AND PR_INP.Pay_Date = CDATE(PaidOn) AND Def_Dept IN ('013')

I've used UNION ALL to other queries and they work fine as well:

SELECT SUM(PR_INP.Futa_5) + SUM(PR_SUTA.Amt_10) AS AMT, '67360' AS DEPT, CDATE(PaidOn) AS PayDate, 'ER Tax Exp:  FUTA/SUTA - Dept 10, 11, 12' AS Memo FROM PR_INP INNER JOIN PR_SUTA ON PR_SUTA.Loc_No = PR_INP.Loc_No AND PR_SUTA.Div_No = PR_INP.Div_No AND PR_SUTA.Emp_No = PR_INP.Emp_No AND PR_SUTA.Pay_Date = PR_INP.Pay_Date WHERE PR_INP.Loc_No = 2170 AND PR_INP.Div_No = 100 AND PR_INP.Pay_Date = CDATE(PaidOn) AND Def_Dept IN ('010', '011', '012')
UNION ALL
SELECT SUM(Fica_5 + IIF(PR_MAST.Gross_Yr >= 200000.00, Medc_6, Medc_5) ), '63330', CDATE(PaidOn), 'ER Tax Exp: Federal, SS, Medicare- Dept 5, 6' FROM PR_INP INNER JOIN PR_MAST ON PR_MAST.Loc_No = PR_INP.Loc_No AND PR_Mast.Emp_No = PR_INP.Emp_No WHERE PR_INP.Loc_No = 2170 AND PR_INP.Div_No = 100 AND Pay_Date = CDATE(PaidOn) AND PR_INP.Def_Dept IN ('005', '006')

I also tried changing the type of the query to a pass-through query, but I still get the ODBC--call failed message.

What's going on here? Why would too queries that run separately generate an ODBC error when unioned together? And how to I fix this so it will run?

May be relevant: http://stackoverflow.com/questions/13177354/union-query-access-on-an-interbase-db — Kristen Waite, Jun 07 '16 at 15:21
Thanks. I actually found that post myself. I tried everything on it, but can't figure out why mine doesn't work since each part of the query works separately. — boilers222, Jun 07 '16 at 15:28
Have you run the UNION ALL query through the Pervasive Control Center or other ODBC tool? Does it work or does it give an error? — mirtheil, Jun 07 '16 at 17:19
Just tried it and, yes, the query works in Pervasive Control Center. The only change was that I hard-coded the date instead of using a variable as I did in Access SQL. — boilers222, Jun 07 '16 at 18:07

score 1 · Answer 1 · answered Jun 08 '16 at 02:11

I don't know for certain, but I can't help wondering whether it relates to the use of MEMO as a column name, given that MEMO is actually a ms access data-type; Logically this shouldn't matter, but with Access, one never knows.

However, be this as it may, your query doesn't need a UNION ALL and can be written as follows:

SELECT Sum(p.Futa_5) + Sum(s.Amt_10) as AMT, 
    iif(Def_Dept = '013', '66360', '67360') as DEPT, CDate(PaidOn) as PayDate, 
    'ER Tax Exp: FUTA/SUTA - Dept ' & iif(Def_Dept = '013', '13', '10, 11, 12') as [Memo]
FROM pr_inp p INNER JOIN pr_suta s ON p.Loc_No = s.Loc_No AND p.Div_No = s.Div_No 
    AND p.Emp_No = s.Emp_No AND p.Pay_Date = s.Pay_Date
WHERE p.Loc_No = 2170 AND p.Div_No = 100 AND p.Pay_Date = CDate(PaidOn) 
    AND Def_Dept IN ('010', '011', '012', '013')

When asking questions it is good practice to alias tables as I have done here, as it makes queries easier to understand, and generally to add line breaks as I have here, to enable an answerer to copy your code elsewhere - I had some trouble copying your code due to the absence of line breaks in it.

This query may run faster if pr_inp in the from clause was replaced with a subquery to only return those rows in pr_inp that you are concerned with (moving the where clause into the subquery). Eg

SELECT (as above)
FROM (SELECT * FROM pr_inp WHERE Loc_No = 2170 AND Div_No = 100) p
    INNER JOIN (as above)
WHERE p.Pay_Date = CDate(PaidOn) AND Def_Dept IN ('010', '011', '012', '013')

score 0 · Answer 2 · answered Jun 08 '16 at 03:22

Couple things to try for troubleshooting:

I just saw @John Bingham mention MEMO too... but I'll include my comments anyways.

MEMO is a reserved word in Access - I doubt that'll cause an ODBC error but if you really need the fieldname, maybe try wrapping "MEMO" in square brackets. You do use it in the query that you say works - so I'm not sure if that's the problem.

Does the query work if you create two separate queries and use UNION ALL with the two queries?

Why do I get an ODBC connection failed error when using UNION ALL in Access?

2 Answers2