MS Access Union Query to mysql back back end

I've got MySQL server 4.1.1 as a back end containing my tables and (was handed) MS Access as the front end solution using MyODBC 3.56.11-2.  I've written some union queries in MySQL Query Browser to gather information from multiple tables.  Since MySQL and Access deal with dates, date related functions, and intervals between dates, differently when I copy/paste some of my queries into Access they fail.  Is there a way I can have Access ignore any problems it thinks I have with my SQL syntax and just dump the query to the server?  It'll get results if it does as the queries work when I test them with Query Browser.

Thanks!

-Jon
VQJonAsked:
Who is Participating?
 
Jokra_the_BarbarianConnect With a Mentor Commented:
Try using a pass-thru query in Access. Then, whatever works in MySQL will always work in Access because it is executed by MySQL, not JET.

0
 
VQJonAuthor Commented:
Sigh...  I dislike Access so much.

But thanks for the answer!
0
 
VQJonAuthor Commented:
Okay wait, is there an easy way to not have to point the my ODBC every time I run the query?
0
 
Jokra_the_BarbarianCommented:
To answer your last question, you can create a pass-thru fix up function:

Public Sub PassThroughFixup( _
  strQdfName As String, _
  Optional strSql As String, _
  Optional strConnect As String, _
  Optional fRetRecords As Boolean = True)
 
    ' Modifies pass-through query properties
    ' Inputs:
    '   strQdfName: Name of the query
    '   strSQL: Optional new SQL string
    '   strConnect: Optional new connect string
    '   fRetRecords: Optional setting for ReturnsRecords--
    '               defaults to True (Yes)
   
    Dim Db As DAO.Database
    Dim qdf As DAO.QueryDef
   
    Set Db = CurrentDb
    Set qdf = Db.QueryDefs(strQdfName)
   
        If Len(strSql) > 0 Then
            qdf.sql = strSql
        End If
       
        If Len(strConnect) > 0 Then
            qdf.Connect = strConnect
        End If
   
    qdf.ReturnsRecords = fRetRecords
    qdf.Close
    Set qdf = Nothing
   
End Sub
0
 
VQJonAuthor Commented:
Haven't tried the public sub yet but I accomplished the same thing by updating the "ODBC Connect Str" property of the query in design view.  Only complaint there is that Access doesn't hash or protect the password but my general user account has very limited MySQL privs anyway.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.