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?

score 17 · Answer 1 · edited Jul 01 '19 at 00:18

17

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.

edited Jul 01 '19 at 00:18

Peter Mortensen

30,738
21
105
131

answered Jun 30 '10 at 18:58

MatthewMartin

32,326
33
105
164

1

Just double checked in Office 2010. You can't. using -- like in TSQL would raise a "Syntax error (missing operator) ..." – MatthewMartin Jun 30 '10 at 19:00
3

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. – Dodecaphone Mar 14 '19 at 21:51
1

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. – Magnus Feb 06 '22 at 19:28

score 12 · Answer 2 · answered Jan 09 '17 at 14:54

12

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' ";

answered Jan 09 '17 at 14:54

mivk

13,452
5
76
69

score 2 · Accepted Answer · edited Aug 30 '21 at 00:22

2

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

.

edited Aug 30 '21 at 00:22

Adrian Mole

49,934
160
51
83

answered Aug 29 '21 at 22:56

santosgcot

36
3

score 1 · Answer 4 · answered Mar 14 '19 at 22:13

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.

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

score 1 · Answer 5 · answered Sep 10 '20 at 14:39

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"

How do I comment SQL code out in Microsoft Access?

5 Answers5

Linked