Hi to all,

I just want to know if the SQL statements created by MS Access when you try to use its Query Builder tool is just as the same as other SQL standards?

I notice that it builds SQL statements differently, especially on JOINS.

Btw, I am using MySQL as my RDBMs and SQL Maestro for MySQL as my GUI tool. It has a query builder just like MS Access and that's how I was able to notice the way they build SQL statements differently.

I would just like to add, anyone know of a better MySQL GUI Tool? MySQL Query Browser is good but it does not remember nor allows you to save your workspaces (tables opened, queries made...etc). Although it allows you to save your queries in a custom file and then open it.

EMS SQL Manager looks good though, but I haven't tested it yet. They say SQLYog is also good? What's your take on MySQL GUI tools available out there?


many thanks!

The query designer in Access does things in its own inimicable way. you can improve its compatibility with SQL by setting ANSI SQL flag in the tools | settings. however the query designer will always do odd things with brackets and use its own version of JOIN's.

when using the query designer you are creating JET SQL queries not ANSI SQL queries. attempting to save queriers will mean you use local queries not server queries.

it may be you n eed to revisit your design and use the MySQL equivalent of stored procedures.

thanks

I am not concerned about the queries, but only on the way MS Access builds them. The way Access builds it, is just like a "query within a query", something that includes a join in inside a join and so on and so on. Pardo me if you don't know what I mean. Its just the way they build joins is not readable for me. Putting each join inside another join makes me confuse.

I do notice that if I set the ANSI SQL flag in the tools | settings, there is no change to be seen.

many thanks

try HeidiSQL

JET has its own syntax for joins.. that syntax is nowhere near ANSI standard, and I doubt ever will be.

When you are designing queries in query designer they are solely targetted at JET. that means you cannot expect to use the SQL in other engines. Im not aware of any tools similar to the query designer that are used elsewhere. in most other SQL environments you have to write the SQL yourself....

That doesn't change the JET join syntax, just brings a few other parts of the syntax more in line with the standard.

I would disagree that JET join syntax is miles away from the standard - it is almost totally standard apart from the addition of those annoying brackets (parentheses). Remove those and you have standard join syntax. I'm not even certain they aren't allowed in the standard - others would be better placed to say. They certainly aren't required by it though.

pootle flump ur codings are working excelent.

You know you can write them yourself, right?

I can write a "little", you know, I am still a newbie in SQL

Removing those annoying parentheses is a good but the way it builds the join statements are really confusing to me. Take a look at how MS Access builds those SQL statements, "INNER JOIN (table A INNER JOIN table b ON....)" and so on.

Is it possible that the SQL syntax (queries that involve JOINS) you use in MySQL can execute on MS Access? I have tried, but Access says that my syntax is incorrect, even if I have tested it and produces a resultset in the mysql command line client.

Thanks to r937 or "rudy" for the HeidiSQL. I'll try it out. Is that the one you use also if your managing MySQL databases?

many thanks

No, not if you do anything incompatible between the two.

pootle flump ur codings are working excelent.

i hardly ever "manage" databases

i run a lot of test queries, though

No, I didn't do anything, my queries did not have any MySQL syntax, just INNER JOINS on 2 tables. Access couldn't understand it. It is good when I tried it in MySQL.

many thnaks