UNION QUERY to MYSQL Fails

[AndrewKKWalker]

I have an Access 2003 database linked via ODBC using MYSQL ODBC version 5.1
It works very well apart from UNION queries with more than 1 UNION

If I run a query with 2 UNIONS then I get an Error

ODBC – call failed. (Error 3146)

The query in Question is like this one

Select  def_Make, def_Reg, 'Defleet' As TRACKER From tbl_defleet  
UNION 
Select  tra_Make, tra_Reg, 'Tracker' As TRACKER From tbl_Tracker  
UNION
Select  arc_Make, arc_Reg ,'Archive' As TRACKER From tbl_TrackerArchive

If I run this on MYSQL then it is fine

If I run it on ACCESS I get the ODBC error

If I run any pair of the Union Such as

Select  def_Make, def_Reg, 'Defleet' As TRACKER From tbl_defleet  
UNION 
Select  tra_Make, tra_Reg, 'Tracker' As TRACKER From tbl_Tracker

It is fine.

Looks like an issue with the MYSQL ODBC Adapter.

BUT...IF I write it as a PASS THROUGH query running on the server with the same adapter DSN, then it is fine.


Anyone any ideas of whether it is possible to get round this issue IN ACCESS
without resorting to temporary tables OR PASS Through query.

[Jezza]

Hi Andrew

Have you tried a UNION ALL

Select def_Make, def_Reg, 'Defleet' As TRACKER From tbl_defleet 
UNION ALL
Select tra_Make, tra_Reg, 'Tracker' As TRACKER From tbl_Tracker 
UNION ALL
Select arc_Make, arc_Reg ,'Archive' As TRACKER From tbl_TrackerArchive

[AndrewKKWalker]

Hi Andrew

Have you tried a UNION ALL

Select def_Make, def_Reg, 'Defleet' As TRACKER From tbl_defleet 
UNION ALL
Select tra_Make, tra_Reg, 'Tracker' As TRACKER From tbl_Tracker 
UNION ALL
Select arc_Make, arc_Reg ,'Archive' As TRACKER From tbl_TrackerArchive

YES.makes no difference.
I have looked into this a bit more and it looks like it is an issue with the MYSQL ODBC Adapter.
I would have hoped for a fix in 5.1 because it didn't work in 3.51,
but it looks like it was not fixed. Probably not high on the TO DO list for them
Maybe when MYSQL 6 is in more use they will fix it in the next ODBC update
assuming there is one. I'll not hold my breath.

Anyway, I have resolved the issue by using a PASS through query,
and getting the SQL updated for it in Code.
It has the advantage of running on the server so actually appears faster.
BUT, it would have been easier if I did not have to write the code again.

Never mind.