Results 1 to 3 of 3
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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

    Code:
    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

    Code:
    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.
    Andrew

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Andrew

    Have you tried a UNION ALL

    Code:
    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
    Jerry

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Jezza View Post
    Hi Andrew

    Have you tried a UNION ALL

    Code:
    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.


    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •