Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Basic Q on using (formatting) SQL (SR1)

    I'm a new user of Access; I've written SQL with other
    databases so would like to just do my work writing SQL in Access.

    Problem: Any SQL I write in SQL design does not remain formatted as entered making it difficult to work with complex queries.

    Am I missing something ? Is there a way around this ?
    (I'd rather not have to get into Visual Basic and et.c...)

    Or:

    Is there some way I can write/store queries in external text files and execute them (or load them as stored procs ?)

    Or: Is there some 3rd-party tool I should be using ?

    Or: ??


    TIA

    Bill M.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Basic Q on using (formatting) SQL (SR1)

    If you are working with an ADP back-end - i.e. SQL Server or the MSDE, then the view/stored proc tools do retain formatting. However the query editing in MDB files always works that way. One trick we have used is to store queries in a table with the appropriate line breaks and indents, and then load them in code and execute them that way. But that's lots of overhead, and you see some hit in performance because the query has to be compiled. The bottom line is that doing queries with the query grid tool is so much faster than typing in the SQL that nearly everyone builds their queries that way. And the tool is reliable as well - a few of the really complex kinds of queries force you to resort to typing SQL - but most developers end up using it almost exclusively. Hope this clarifies things some.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Basic Q on using (formatting) SQL (SR1)

    This probably occurs because you displayed the QBE grid. When Access switches between the grid and SQL view, it does it own formatting of the SQL statement. If you save the query from the SQL view, the next time you open it then it will still display in SQL view without the reformatting.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Basic Q on using (formatting) SQL (SR1)

    Thanks for the answers...

    I'm using Access 2000; Saving in SQL view and then re-opening does remain in SQL view but does not save the formatting (unless I'm missing something).

    My main problem is that I know how to do something in SQL but it's taking me a little while to get familiar with the details of grid design view.

    Bill M

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Basic Q on using (formatting) SQL (SR1)

    I'm used to Access97, and there may be some differences to how Access2000 saves/displays in SQL view. Also, I'm not exactly sure what you mean by "formatting".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Basic Q on using (formatting) SQL (SR1)

    There's no difference in behavior between the two versions in that respect. The only queries that retain their formatting (carriage returns, indents, etc.) are DDL and DML queries created using the SQL Specific option from the Query menu in the query editor. Even those will lose their formatting if you open them in design view like a regular query.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Aug 2001
    Location
    New Jersey, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Basic Q on using (formatting) SQL (SR1)

    By "formatting" I mean "pretty printing": the use of carriage-returns, indents, blank lines, etc to ease the deciphering of the SQL.

    Now that I've used the design window a bit I do agree that it is more convenient than writing "raw sql". (Access seems to do a reasonable job of hiding the details of SQL syntax).

    However, I find that I do go to the SQL window to answer "why doesn't the query do what I want?",

    <soapbox on>
    Therefore I wish that the SQL were presented in a better fashion. I find myself putting in returns, etc at critical places to be able to make sense of the SQL for more complex queries.

    I should not have to do that.

    I'm a believer in the statement: "Computers are for making my life easier and not vice versa" and thus can bet pretty rigorous about User Interface design.
    <soapbox off>

    Bill M

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Basic Q on using (formatting) SQL (SR1)

    What you're running into is an essential difference between SQL Server (or any other database server) and Access (or any other desktop database). The desktop products have to do something with their SQL, they can't just execute it the way you can in a database server. That being the case, all they see is a long string of text that the query engine attempts to handle. Formatting is irrelevant to the query engine, only syntax counts.
    Charlotte

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Basic Q on using (formatting) SQL (SR1)

    You wrote:
    >>By "formatting" I mean "pretty printing": the use of carriage-returns, indents, blank lines, etc to ease the deciphering of the SQL.<<

    Yeah, you will lose most of that type of formatting, unfortunately. Access97 seems to retain carriage-returns, but not any other formatting. And this assumes you don't toggle between SQL view and Design view; since every time you toggle back to SQL view, I think Access rewrites it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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