will the sql queries i run in ms-access also work on mysql without any changes ?

Question

will the sql queries i run in ms-access also work on mysql without any changes ?

Answer 1

It's possible, but it depends on what the queries use. Date and string functions are the most likely to cause problems when porting queries.

The DATEDIFF keyword is supported on both Access & MySQL, but the function takes different parameters:

• Access: DATEDIFF
• MySQL: DATEDIFF

Answer 2

I would like to add one more point to OMG Ponies answer

Transform that is use for cross tab queries in MS ACCESS cannot be used in MySQL

e.g.

TRANSFORM Sum([M_Sales].[Amount]) AS SumOfAmount 
SELECT [M_Sales].[Department] 
FROM M_Sales 
GROUP BY [M_Sales].[Department] 
PIVOT Format([M_Sales].[Sale_date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

in MSACCESS ( taken from )

could be something in MySql Common MySQL Queries . Just visit the Pivot table section

Answer 3

Well, if the coder wrote the queries with portability in the forefront of their mind then there's a good chance that you will need to make only minimal changes. However, you could only expect the most simple queries to work with no changes, regardless of which SQL product were involved.

In an ideal world, all SQL products would comply with ISO/ANSI Standard SQL with vendor extensions. In reality, while mySQL generally has a good track record in Standard SQL compliance, the Access Database Engine's record is rather poor -- it still doesn't even conform to entry level SQL-92, which was a fairly fundamental requirement even a decade ago (and seemingly none too difficult to achieve either).

[Your question is in all lower case. I've assumed by 'queries' you mean SQL DML SELECT. If you use 'queries' to mean INSERT/UPDATE/DELETE SQL DML plus SQL DDL and SQL DCL then this changes the answer. You should note the the Access Database Engine's UPDATE SQL DML is proprietary and non-deterministic; further, it does not support SQL-92's scalar subquery syntax. This is of major significance when porting to a SQL product.]

Thanks for your question. It just goes to show that it's worth considering portability from day one.

Answer 4

Given some of your previous questions, you could save some time with MySQL, compared to Access: 12.1.10. CREATE TABLE Syntax