MS Access Union Query to mysql back back end

Posted on 2005-04-14
Medium Priority
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
  • 3
  • 2
LVL 11

Accepted Solution

Jokra_the_Barbarian earned 500 total points
ID: 13782454
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

ID: 13785601
Sigh...  I dislike Access so much.

But thanks for the answer!

Author Comment

ID: 13785625
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

ID: 13785958
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

ID: 13791552
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.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

830 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