MS Access Union Query to mysql back back end

Posted on 2005-04-14
Last Modified: 2008-02-01
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.


Question by:VQJon
    LVL 11

    Accepted Solution

    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.


    Author Comment

    Sigh...  I dislike Access so much.

    But thanks for the answer!

    Author Comment

    Okay wait, is there an easy way to not have to point the my ODBC every time I run the query?
    LVL 11

    Expert Comment

    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
        Set qdf = Nothing
    End Sub

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now