Can I put comments in a query?

[MadJock]

Subject line says it all! I would like to explain what the SQL behind some more complex queries is doing.

Thanks,

Graeme website:

http://www.graeme-taylor.co.uk

 

[Quehay]

In the queries window you can right click on any query object and see a description window for any of them. (Unfortunately, like table field descriptions, the blasted things are NOT exposed in DAO, that I can tell, which makes it impossible to extract them in code for documentation).

 

[LittleSmudge]

The simple answer is NO - but the workaround depends on how you are building up your SQL statement

If you use
Dim strSQL As String
strSQL = "SELECT field1, field2, field3, " _
& "field4, field6 " _
& "FROM tblOne, tbl2 " _
& "ON tblOne.field1 = tblTwo.field4 " _
& "WHERE field6 = 47 " _
& "AND field3 '" & control3 & "';"

Then you have to put all the comments at the start or the end.

However if you use

Dim strSQL As String
strSQL = "SELECT field1, field2, field3, "
' Then you can add
strSQL = strSQL & "field4, field6 "
' as many comments
strSQL = strSQL & "FROM tblOne, tbl2 "
' between these lines
strSQL = strSQL & "ON tblOne.field1 = tblTwo.field4 "
' as you see fit
strSQL = strSQL & "WHERE field6 = 47 "
' etc
strSQL = strSQL & "AND field3 '" & control3 & "';"

Personally I think the first option ends up easier to read with the full explanation at the start. But it's a choice thing in the end.


'ope-that-'elps.

G LS

 

[MadJock]

Thnks guys,

That has answered my question. What I was hoping for was to have something like this:

--Get all the farmer details
SELECT * FROM tblFarmer

In SQL view at the query window.

Oh, well! website:

http://www.graeme-taylor.co.uk

 

[LittleSmudge]

Oh. Well in that case the answer is a big fat hairy NO. :-(

 

[Quehay]

OK now I get you...
I've got a Jet SQL reference in hand (I'd include link but I don't remember where I got it--probably MS KB). I can't find any reference to a comment delimiter. Which really stinks--this would be really useful.

 

[cmmrfrds]

You can't see it this way
--Get all the farmer details
SELECT * FROM tblFarmer
but you can go up to properties on the menubar while in SQL View and add the description.


There is an additional description if you are in design view instead of the sql view. Also, you can right click on the query and add the description.

This description is also available in the properties collection for the query, so, you should be able to get it from either DAO or ADO.

 

[cmmrfrds]

Here is a function to get information about objects in Access 2000. This is using ADO and you could modify to meet your needs.

Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type
' If tb.Type = "TABLE" Then
If tb.Type = "VIEW" And tb.Name = "max4fields" Then
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
Next
End If
Next

End Function

 

[Quehay]

cmmrfrds is right about the DAO QueryDef properties (and offering redundant advice about the Query Description property).

Try this to see the Description properties for queries (although this still doesn't give you the inline SQL commentary that Access really should allow):

[tt]
Public Sub QueryDescriptions()

Dim Qdf As DAO.QueryDef


For Each Qdf In CurrentDb.QueryDefs

Debug.Print Qdf.Properties(16).Value



Next
End Sub[/tt]