Comments in SQL

Is there a way to add comments to a Access SQL string. For example between
UNION to comment on what data each UNION is adding.

Re: Comments in SQL

I have been able to add comments in the Query Sql window by adding a
single quote ' and comments after that -- after the sql for the query

Select * from tblA
Union All
Select * from tblB
' comments -- tblA old data -- tblb new data

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Comments in SQL

"Rich P" wrote in message
news:474c55f5$0$508$815e3792@news.qwest.net...
> I have been able to add comments in the Query Sql window by adding a
> single quote ' and comments after that -- after the sql for the query
>
> Select * from tblA
> Union All
> Select * from tblB
> ' comments -- tblA old data -- tblb new data
>
> Rich
>
> *** Sent via Developersdex http://www.developersdex.com?wp_ml=0 ***

Thanks Rich

Single quote did not work in Access97.

Re: Comments in SQL

paii, Ron wrote:

> Single quote did not work in Access97.

Access queries do not support comments unless they are passthrough queries
to a server databse that does support them.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Comments in SQL

paii, Ron wrote:
> "Rich P" wrote in message
> news:474c55f5$0$508$815e3792@news.qwest.net...
>
>>I have been able to add comments in the Query Sql window by adding a
>>single quote ' and comments after that -- after the sql for the query
>>
>>Select * from tblA
>>Union All
>>Select * from tblB
>>' comments -- tblA old data -- tblb new data
>>
>>Rich
>>
>>*** Sent via Developersdex http://www.developersdex.com?wp_ml=0 ***
>
>
> Thanks Rich
>
> Single quote did not work in Access97.
>

I tried this in A97
Set qdf = CurrentDb.QueryDefs("Query1")
strSQL = "Select * From Table1 [Test this comment out]"
qdf.SQL = strSQL
Docmd.OpenQuery "Query1"
and it worked. It basically made the Table1 1 the alias of the comments
by converting it to
SELECT * FROM Table1 AS [Test this comment out];

As soon as I added Where or OrderBy clauses it choked.
SELECT * FROM Table1 Order By ID '[Test this comment out]"

But if you keep the comments as an alias
strSQL = "Select * From Table1 [Test this comment out] Order By ID"
it appears to work.

Here's the code I used for testing
Sub QueryComments()

Dim qdf As QueryDef
Dim strSQL As String
Dim strHold As String

Set qdf = CurrentDb.QueryDefs("Query1")
strSQL = "Select * From Table1 [Test this comment out] Order By ID "
strHold = qdf.SQL
qdf.SQL = strSQL

MsgBox "Old SQL " & vbNewLine & strHold
MsgBox "New SQL " & vbNewLine & qdf.SQL

Set qdf = Nothing

DoCmd.OpenQuery "Query1"

'comment the rest below to save the new query else keep to reset
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = strHold
MsgBox qdf.SQL
Set qdf = Nothing

End Sub

Re: Comments in SQL

paii, Ron wrote:
> Is there a way to add comments to a Access SQL string. For example between
> UNION to comment on what data each UNION is adding.
>

Stephen Lebans has an interesting approach to this problem:

http://www.lebans.com/addsqlcomments.htm


--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft MVP
'--------------------------