Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    How MS Access builds its SQL statements

    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!
    Programming is fun!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    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
    Programming is fun!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    What's your take on MySQL GUI tools available out there?
    try HeidiSQL

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panoy
    I do notice that if I set the ANSI SQL flag in the tools | settings, there is no change to be seen.
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    You know you can write them yourself, right?
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by gvee
    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
    Programming is fun!

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Panoy
    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.
    No, not if you do anything incompatible between the two.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    Is [HeidiSQL] the one you use also if your managing MySQL databases?
    i hardly ever "manage" databases

    i run a lot of test queries, though

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by pootle flump
    No, not if you do anything incompatible between the two.
    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
    Programming is fun!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •