I know I have seen this on this site but cannot find it by search. :(

I quite often bring SQL from SQL Server ( 2K & 2K+5 ) into Access. Usually it runs clean with only minor changes like COALESCE needs to be changed to NZ and the like. I heavily comment every thing I do both in VB and SQL. Of course VB the single quote is substitute for REM as '--' comments a single line in SQL. I know of no way to create a paragraph comment in VB or Access SQL ie '/* */' pairs.

What I would like to know is if there is a way to make comments in Access SQL? Single quote or double dashes ALLWAYS create an error.

(Also it would be kewl if multiple queries could be run in one query like SQL Server - oh well, wish list ;) )

Thanks

Hi -

Don't know if this is what you're looking for:

http://www.access-programmers.co.uk/forums/showthread.php?t=147055

Bob

Nice tool. I like how it retains formatting of your SQL also.

I always cringe whenever I see my nicely formatted SQL get turned into a stream of text the next time I open it up. I wonder why Microsoft doesn't have this type of functionality out of the box.


I couldn't agree more. And also inserting all of those nasty parens that serve no reasonable purpose. I would really like to try the tool but am not willing to give my information or have another login kicking around. And the purchase price of $160 is pretty steep too :eek:
You did provide the answer that apparently, there is no way to insert comments to Access SQL :(

I've been aware of that addin before, never tried it myself either.
Fundamentally - it's methodology of storing formatted SQL separately as just text is the only way.
Jet supports neither formatting not comments in its query statements.
(Access will maintain some limited formatting - but there's no guarantee of that).

I personally feel MS will introduce formatted SQL for Access (if not Jet) at some stage. Demand for it is too high that it won't feature eventually.
I'd still not necessarily expect this to include comments when it comes. It might - you never know. But fundamentally you need the separation as that addin offers - as Jet doesn't support them (or multiple statements etc ;-)

When I first read about how MySQL was capable of "extending" their extensions to environment outside of MySQL, I got excited at the possibility.

For example, suppose MySQL had a specific keywords 'FOR UPDATE', which locks a specific selected row with an exclusive lock so the query would be:

SELECT * FROM foo FOR UPDATE;

This would have to be in a passthrough query, but MySQL can support this by "hiding" the extension in a comment:

SELECT * FROM foo /*!FOR UPDATE */;

The bang operator indicates to the server that this isn't actually a comment and to parse it as MySQL's sql, which would theoretically allow me to use normal queries and pass off the extension off to MySQL.

So, I'm just a bit teensy disappointed that it won't be happening.

But gotta make lemonade.

Where would I go and give them a holler that I would very much like to have support for comments, especially one that will be passed along to the backend?

You're wanting comment in Jet queries - and those to be passed to the server for interpretation by MySQL?
I can't see that happening at all to be honest.
Even if Jet (or just Access) were to implement comments - I doubt we'd see them passed along to the ODBC provider. That's *if* we even get comments at all. Which, as I indicated earlier, of all possible SQL enhancements - I'm not the most confident about getting.
You've got Passthroughs still though. :-)

If you want feedback to go directly to the Access Team - then the official team blog is likely the best bet. Though do try to find (/wait for) a thread that is applicable to the subject matter. I know I wince when reading it and any just old subject gets segwayed into a post feedback.

Leigh,

Thanks for the pointer RE: feedback.

To be honest, I was expecting just that- comments being dropped before passed off to the parsers, but I certainly didn't expect total lack of support for comments in query. But after thinking about it, it does kind of make sense- Considering that Jet uses DAO, whether with or without Access, and all QueryDef (and any other object) generally has a description property, there is really no need to embed comments in query within Jet's environment.

Then there's issue of difference in rows count- if I sent what Jet thought should return 20 rows, but server did some extra parsing and returned only 10, it'd make for some interesting fun! So, yes, I'm probably asking for a pie in sky here.

The idea of using comments was that I'd get a updateable query that would be bound to form, without confusing Jet with MySQL-specific (or even any other database providers' extensions... for that matter) extensions.

I couldn't agree more. And also inserting all of those nasty parens that serve no reasonable purpose. I would really like to try the tool but am not willing to give my information or have another login kicking around. And the purchase price of $160 is pretty steep too :eek:
You did provide the answer that apparently, there is no way to insert comments to Access SQL :(

I pulled my horns in and made the purchase. The product is just too good and the pain of continuing with Access myopic interface too severe - actually buying two licenses: office and home. :cool: