Is it possible to add a comment to SQL in Access

[dewsbury]

Hi,

My Access SQL code is getting long and complicated due to multiple tables etc.

Can I add comments into the code just for my own reading?

All help appreciated.

 

[Norie]

As far as I know this isn't possible.

Why not split the query up into several other queries with descriptive names?

Can you post the SQL?

 

[dewsbury]

Thanks Norie,

I was afraid of that!

I appreciate the point about splitting the code up with better names etc.

(I will not send sample code because it was more a general query).

Thanks again

 

[mdmilner]

ok

Here's a pointed question.

Just how are you saving your SQL statements?

Do you stash them into a table and extract them from there (central point of reference)?

Do you build the SQL from code (querydef)?

Or do you build the SQL and just use them, referenced by name.

Mike

 

[dewsbury]

Hi Mike ,

I am not sure I full understand the question but here is my reponse.

I use access 2003.
I click on queries, the click on "new" and then use drag & drop to generate my query.
Sometimes I then go into "View" "SQL View" and modify my query using conventional SQL syntax.

I save my query by simple closing the window with the SQL in it.

My SQL is generally no longer that 10 (?) lines long.

Hope I am making sense!

 

[mdmilner]

try this

Right click the query name under the Query tab.
Select properties and type something into the box that appears.

Instant "comment" visible only in the window display.

The questions were based on different more technically complex techniques.
Basically, instead of maintaining unique query objects, add the text of the SQL statement into a field in a table. Then, open that table and extract the information, dynamically build the query and use it.

Aka, it never resides under that query tab.

Mike

 

[Gates Is Antichrist]

That is a great approach. I use the "property" comment field heavily but I like your sophisticated angle.

He might also benefit from your post
http://www.mrexcel.com/board2/viewtopic.php?t=137572
showing how simple it is to build queries in VBA. If his queries are becoming extensive, they might possibly be maintained more succinctly in code (and, hey, with the bonus of unlimited commenting there, no less).

 

[dewsbury]

Thank you all!
That will do nicely!

 

[mdmilner]

an example

Here is one example of what I'm talking about.

http://www.mvps.org/access/queries/qry0014.htm

As summary, this technique illustrates how to put your queries into a table, and then recall the information and use it in a SQL statement. The technique is valid for any use of SQL...particularly when you want to get a recordset object or if you wish to create the queries (querydefs).

In my personal opinion, at this time, I've outgrown this method somewhat.
What I've found is, particularly because of size (you can only fit 255 characters into a single text field) that I ended up needing to split up many queries. It was easier to just make a custom function to do a specific task.
I also found that I commonly used all the fields in a given table...in the exact order they were in that table.

The latter lent itself to a different approach. Open the table and walk through the field names (using tabledefs) and create a list in the proper syntax for fieldnames in a SQL statement...then dynamically throw in the rest of the key syntax.

Here's an example of the latter. I've been reusing (sometimes minor modifications as I identify weaknesses in the function design) the below in various places.

Public Function Assembler(ByVal strMode As String, ByVal tblName As String, _
                        Optional ByVal tblSource As String)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()

Select Case strMode
  Case "delete"
    strSQL = "DELETE * FROM " & tblName
  Case "append"
    strSQL = "INSERT INTO " & tblName & " ( " & GetFlds(tblName) & " ) "
    strSQL = strSQL & "SELECT " & GetFlds(tblSource) & " FROM " & tblSource
  Case "make"
    strSQL = "SELECT " & GetFlds(tblSource) & " INTO " & tblName & " FROM " & tblSource
  Case Else:
End Select
DoCmd.RunSQL strSQL

Set rs = Nothing
Set dbs = Nothing
End Function

Public Function GetFlds(ByVal myTable As String, Optional ByVal myType As String) As String
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim x As Integer

Set dbs = CurrentDb()
strSQL = "SELECT * FROM " & myTable

Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
  For x = 0 To .Fields.Count - 1
    Select Case .Fields(x).Name
      Case "Date", "Now", "Field"
        GetFlds = GetFlds & "[" & .Fields(x).Name & "], "
      Case Else:
        GetFlds = GetFlds & .Fields(x).Name & ", "
    End Select
  Next x
End With

GetFlds = Trim(Left(GetFlds, Len(GetFlds) - 2))

End Function

As a note, I'm thinking these days of moving to a hybrid method as my "standard". Use the above Functions BUT, use a table that holds a unique query name in a field with the next few fields being the different items needed (table Names, parameters, etc). Most likely this would be a two table relational setup allowing unlimited table names, parameters, etc (multiple table names would be a "clue" that I needed to join the two tables together on a keyfield)

Mike