Allow JET/Ace SQL To support Multiple SQL Statements and comments.
This will make the supported CREATE PROCEDURE statement begin to be actually meaningful.
Also, permit the Query Editor SQL view to RETAIN WHITESPACE & tabs used for FORMATTING (indenting) in the SQL. to enhance readability.
Patrick Honorez commented
I wouldn't mind NOT to be able to switch back to design view once I manually edited my SQL, if that is the price to pay to keep my readable SQL...readable.
Ben Sacheri commented
I would like to recommend you look at the "Access SQL Editor" add-in from Field Effect, LLC which supports:
◾Search-and-replace, with regular expression support
◾Poor Man's SQL Formatter integration
(I am a user, not the author)
Moshe Arzt commented
for the time being I highly recommend the following add-in (I am a user not the creator):
Jack Leach commented
There's a similar suggestion for comments/formatting here that's under review, perhaps they can be merged (or if you're more worried about comments than multiple statement support, move the votes there?)
Tom Wickerath commented
Way overdue, in my opinion.
Any comments. Oh wait, yes comments comments comments !
Doug Y commented
Comments and formatting are the big ones for me. While it will be nice to have multiple SQL statements in one query, I can work around it.
Jack Leach commented
COMMENTS! I have to keep a separate external file for any complex queries that contains the comments else I loose valuable documentation. This is a nightmare to maintain, and all that need be done is find "--" that's not part of a literal and chop the rest off for processing (I don't care about block comments - while nice, the line comments are the only ones that really kill to not have).
Mark Burns commented
I understand the difficulty of this request as there is a tight relationship between the SQL view and the Query Designer views. I think it would be acceptable to allow the designer to obliterate the formatted SQL, so long as warnings were displayed before the changes were committed that will erase the formatted sql (and comments). Multiple statements could be handled in the designer simply by adding a vertical scrollbar to the right of the designer window (to scroll the multiple SQL statements). If the designer were to pop up a "Warning the SQL is in a formatted state, making changes in the designer will remove all SQL formatting." would be sufficient to allow people to decide what to do and how, or not. If you really wanted to go the extra mile, allow a special comment or something to bar the query editor from altering a formatted SQL statement. That way WE could control what things the query designed was allowed to "mess up" or not.
Jenny Duskey commented
Find & Replace would probably have saved me more time over the past few years than any other improvement to Access Desktop.
We CLEARLY need an enhanced editor with Find & Replace. Wherein there are 3rd Party tools, the ones I've tried are quite buggy.
Yes on retaining formatting!!! I am out of votes or I would vote for this too!
Find and Replace is another great idea -- I currently copy to Word or NotePad, change SQL, then put it back.
Kent Gorrell commented
In fact the SQL view for queries requires a lot of work. Currently doesn't even have a find and replace! But yes formatting would be great.