Why do I get an ODBC connection failed error when using UNION ALL in Access?
<p>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:</p>

<p><a href="https://i.stack.imgur.com/DK9Gy.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DK9Gy.jpg" alt="enter image description here"></a> </p>

<p>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?</p>

<p>Here's the query that doesn't work:</p>

<pre><code>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')
</code></pre>

<p>If I run them separately, there is no error:</p>

<pre><code>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')
</code></pre>

<p><a href="https://i.stack.imgur.com/EVo6s.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EVo6s.jpg" alt="enter image description here"></a></p>

<pre><code>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')
</code></pre>

<p><a href="https://i.stack.imgur.com/0OCJH.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0OCJH.jpg" alt="enter image description here"></a></p>

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

<pre><code>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 &gt;= 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')
</code></pre>

<p><a href="https://i.stack.imgur.com/02Dfj.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/02Dfj.jpg" alt="enter image description here"></a></p>

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

<p>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?</p>