Adding comments into SQL code (1 Viewer)

I would like to insert comments into my sql code (select query > sql view window).

I tried with usual sequences such as /* ... */ but it doesn't work.

Does anyone knows the right sequence ?

Thanks.

in access you start your line with the " ' " sign
exemple :
'********* This code is to build something i realy need *******

Then you start your code right under

'********* End of exemple*******
dont comment in middle of lines...your codes will be a mess...

Yes, it works with VBA code in a VB editor window.

But it doesn't work with SQL code in a SQL view window ...

You could always add info on the query in the properties/description box from the query.

Yes, it's a good idea.

But imagine you have a 50+ lines union query, it would be very useful to let comments in the middle of the SQL. I'm sure everybody agrees on that. But how to do it ?

I tried different things to add comment in the sql view but nothing seems to work tho. Maybe it's just not possible in access. If you find out how to do it, be sure to let us know

Continuation lines in the middle of a statement are not allowed in Access.

You can use two ways to add the comments in the SQL.
--Select * From Table
or
/* Any
'Query */

Hi Nancy
Firstly welcome to the forum and for resurrecting a very old thread

The solutions you mentioned do indeed work perfectly in a SQL server script.
However they are both invalid in Access and will create errors if you try to use them in the VBE or the SQL view of the query designer which is the focus of this question

I checked, and here is the problem. Given the age of the original post, I'm pretty sure that SQL 92 was the standard applicable at that time. Might still be right for ACE but I haven't looked. I was more interested in the syntax issue than details of which standard is in force now.

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

From the SQL92 language specification, it appears that a "comment introducer" is referred to with that dreaded phrase "implementation dependent." I.e. It is possible that Access SQL (or really, JET SQL/ACE SQL) has the option to pick ANYTHING THEY WANT as the comment marker and as long as it doesn't conflict with the rest of the syntax, it can be anything. And that EXPRESSLY includes "nothing."

So what works in SQL Server doesn't have to work in Access / JET / ACE. The fact that /* ... */ works in some SQL implementations but not in others? Not a problem. Not even SLIGHTLY a problem.

I think part of the issue is that Access's JET and ACE don't store sequences of queries as a stored procedure, whereas some of the active SQL engines (ORACLE, ShareBase, Sybase, etc. - then and now) allowed stored sequences of SQL statements to be triggered by activating the single procedure name.

JET and ACE do not store native sequences of queries. All queries are passed to them by the Access environment. And the Access environment uses macros or VBA to run query sequences. Therefore, comments are tagged on the querydef as an object (because all objects can have descriptions), or on the steps of a macro (because a description is allowed for each macro step), or on the VBA instruction that executes the OpenQuery or DB.Execute or whatever is launching the individual query.

Basically, the person asking the original question was "looking for love in all the wrong places."

Or more simply, Nancy's suggestions won't work in SQL view of the query designer because the SQL statement must
1. Start with SELECT, INSERT, UPDATE or DELETE ....or it seems PROCEDURE (never used that myself)
2. Not have any characters after the semicolon at the end
3. Not be split by any characters such as a comment in the middle.

So that cuts out comments being added at the start, middle and end using any kind of notation.
There's nowhere else left to put them

I generally build MySQL Statements in a function, then you can add as many comments as you like!

In fact I've been meaning to blog about it, I think it was Greg, showed me how to do nested SQL Statements, doing them written out SQL Statements is just an absolute pain! I put those into a function and it's easy, well not quite easy, but easier!

So that cuts out comments being added at the start, middle and end using any kind of notation.
There's nowhere else left to put them

Click to expand...

Actually, there is - but not in the SQL text. As I described, you can put a description on every named object visible via the Access object navigation panel. But not in the actual object. You put it in the object's descriptor, which is like putting a comment on the outside of the box, not on what's in it. Thus my original comments.

Hi Doc
I use object descriptions myself at times but ignored that as it had already been mentioned in post 4 & then by yourself.

Some years ago I tried to find out where the descriptions were stored so I could include them in a query. I wrongly assumed the descriptions were in MSysObjects system table along with name, object type, date created/modified. Not so.
I them looked in all other system tables that I could view either directly or using a query. Still no luck.

So perhaps the info is in one of the inaccessible system table such as the many MSysComplexTypes .... tables OR possibly the mysterious f_ ....(long string here) ..._Data table

Examining the ACCDB file with a text/hex editor, the object descriptions can be found if you search. However this is of no use for inclusion in a query

The other approach is to enumerate the properties to a table using a recordset. For example, see the function ListObjProps created by Joel S here: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_other-mso_2010/access-2010-view-object-descriptions/a2de1fe4-ac8b-45bf-8025-675a10c0b04b?tab=AllReplies#tabs

BUT the question remains, why is it so complicated to make use of this particular item of information?

You can always write your sql to a table, including comments. Then have a bit of code that removes the comments before passing to a querydef/recordset/execute.