How do I comment SQL code out in Microsoft Access?

Question

Is it possible to comment code out in the SQL window in Microsoft Access?

Claudication

Answer 1

Depending on your needs you can use the "Description" field on the query "Properties" dialog box:

.

Bibbye

Answer 2

No. You cannot have any extraneous text in Microsoft Access (JET-SQL).

You can make some constraints ignored, e.g.,

Where 
name = "joe"
OR
(state = "VA" AND 1=0)

But that technique is a rather limited way to hide existing SQL.

Novara

Just double checked in Office 2010. You can't. using -- like in TSQL would raise a "Syntax error (missing operator) ..." – Novara

I got my SQL start in Access; it's surprising to discover just how limited it is when going back to it after years of Google BigQuery, MySQL, and SQL Server. – Randolf

I made a tool for that in Access. Initially it was mainly a tool to simplify my work so that I could write my SQL in a more decent editor, like Notepad++. I don't like to use the SQL designer in Access, and its SQL editor is very primitive. So while I was anyway making this tool I also made it so that comments are allowed, like -- and /* */. It also allows me to test queries with various parameters etc. – Fritzfritze

Answer 3

As MathewMartin said, you can't. I use the following workaround:

SELECT * FROM x
WHERE "-- your comment. This plain string is always true";

or

SELECT * FROM x
WHERE y = 'something'
AND " -- z = 'something else' ";

Loxodrome

Answer 4

Access gives you the option of invoking queries from a VBA sub, which obviously can be commented to your heart's content:

' Ensure that the AddressCurrent in tblAddresses only has one item marked.
' Assume the latest.

strSQL = _
    "UPDATE tblAddresses " & _
    "SET AddressCurrent = 0 " & _
    "WHERE AddressCurrent = True "
' A comment can go in the middle if need be!
strSQL = strSQL & _
    "AND AddressNumber NOT IN " & _
         "(SELECT MAX (AddressNumber) " & _
         "FROM tblAddresses " & _
         "WHERE AddressCurrent = True);"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

While having to run a macro that uses DoCmd might seem slightly tedious, it does compensate with other advantages; I've listed a few examples below.

1. Possibility of dynamic scripts
2. Ability to bind the execution of the SQL to form buttons and other controls
3. Locked white space, making queries actually easier to read

Randolf

Answer 5

Another option would be to have a separate table named 'README' where you can have 2 fields i.e. ObjectName, Comments

This way you can have a memo field where you can mention important points regarding the SQL e.g. "Replace * with [Fname] to get only Full Name"

Lorri