[Webinar] Streamline your web hosting managementRegister Today


Access queries with parameters to MySQL views?

Posted on 2012-08-20
Medium Priority
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
  • 3
  • 2

Expert Comment

by:Evan Cutler
ID: 38313623
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 stored...it 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.)


Author Comment

ID: 38313637
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?

Author Comment

ID: 38313755
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?

Accepted Solution

Evan Cutler earned 2000 total points
ID: 38313776
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)

Author Closing Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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, …
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
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…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

612 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