Access queries with parameters to MySQL views?

Posted on 2012-08-20
Last Modified: 2012-09-04
Hi All,

I am wanting to convert a large access database that includes tables and queries only, to MySQL. I'm done with the tables part. Converted nicely into an sql dump file.

But what about queries? Is this a manual process where I have to copy/paste the sql syntax into MySQL views wizard?

I've been told queries with parameters won't work. Any recommendations for a monster like this one:

PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT Jobs.JobID, Jobs.EstimateStatus, Jobs.PaintStartDate, Jobs.AcceptedPrice, iif(isnull(Jobs.JobLaborCost),0,Jobs.JobLaborCost) AS JobLaborCost, iif(isnull(Jobs.JobMaterialsCost),0,Jobs.JobMaterialsCost) AS JobMaterialsCost, iif(isnull(Jobs.TotalTouchUpCosts),0,Jobs.TotalTouchUpCosts) AS TotalTouchUpCosts, iif(isnull(Jobs.TouchUpMaterialsCost),0,Jobs.TouchUpMaterialsCost) AS TouchUpMaterialsCost, iif(isnull(Jobs.TotalProfit),0,Jobs.TotalProfit) AS JobProfit, Jobs.CrewForeman, Complaints.ProblemType, CStr(DatePart("yyyy",Jobs.PaintStartDate))+IIf(DatePart("ww",Jobs.PaintStartDate)<10,'0','')+CStr(DatePart("ww",Jobs.PaintStartDate)) AS WeekNo
FROM (Jobs INNER JOIN Users ON Jobs.CrewForeman=Users.UserID) LEFT JOIN Complaints ON Jobs.JobID=Complaints.JobID
WHERE (((Jobs.PaintStartDate) Between [StartDate] And [EndDate]) AND ((Users.UserType)=3));

I also have queries that call to other queries like this:

SELECT PainterID, Jobs.JobID, SumWageHours, SumWageDollars, Jobs.AcceptedPrice, Jobs.TotalProfit AS JobProfit, Jobs.EstimateStatus, PaintWage.WeekNo
FROM PaintWage LEFT JOIN Jobs ON PaintWage.JobID=Jobs.JobID;

That SumWageHours and SumWageDollars are separate queries getting called like tables in the SELECT statement. Is that going to work?

It's a long story why I can't just try this and answer for myself. It's a lot of work to test this for various reasons. So before I go down that road, I'm looking for some expert advice on these issues and any snafus I should look for...

Thank you

Question by:billium99
    LVL 9

    Expert Comment

    by:Evan Cutler
    I just did this myself.
    As long as you set your links back into the Access, with the correct table names, the Queries will work.

    The only thing links will do is tell the access file where the actual table is has no bearing on how the table is viewed by other access objects.

    Just make sure the table name does not change (ie.  table1 turned into MySQLtable1.)

    LVL 1

    Author Comment

    Hi - I'm not sure I understand what you mean. So I would keep a copy of the mdb, delete all the tables out of it, and then keep a connection file to that mdb for the sake of those queries? But pull from the MySQL instance for all other data?
    LVL 1

    Author Comment

    Also will this cause an issue if I have potentially thousands of concurrent users? One of the reasons I want to ditch Access is for the perceived limits in performance under heavy loads. If i'm only using Access for those queries, is that eliminating enough Access-specific traffic to still realize the long-term gains in performance that I'm after?
    LVL 9

    Accepted Solution

    if you use the table link function from access...
    and link the tables back to MySQL...

    then you have a front-end, back-end relationship between access and MySQL.

    to relink the table that you moved to MySQL back to Access, you can use the Data Import (ODBC Connection) and select "Link Table" (not Copy Table).  then you will be asked for ODBC Connection requiremetns.

    When that is done, your table is "Linked"  (like a shortcut).

    All queries that reference that table are compiled THEN sent to MySQL.  
    You can copy the front-end to whomever needs it.

    performance should be handled by MySQL based on the type of queries.
    (ie.  Indexes on tables in MySQL based on queries saved on ACCESS)
    LVL 1

    Author Closing Comment

    Thanks. I haven't had time to try this but I don't want to keep this open any longer.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now