Commenting Queries (All)

Topic

New Reply

Hi all,

I work intensively on databases during a time period, then leave them alone for some months, then need to rework a query or create similar queries for another database.

What happens then is that since Access does not support commenting SQL code it takes longer for me to recall what ever I was trying to do, especially when handling complex queries with subqueries or other variants. I know one can ‘comment’ on a query by right clicking on its name | Properties and adding a convenient description, but that does not quite compare to commenting the code.

I was wondering what other Loungers do to keep track of their work. Especially if any of you have found a way round to commenting SQL code.
Maybe you suffer just like me, but I’d rather be optimistic in light of all the brilliant ideas I’ve read so far.

Thank you in advance

Reply | Quote

Replies

I usually create a Word document in which I document a database and its objects.

For queries, I list its SQL statement, followed by a short description of its purpose / use, and if necessary comments on specific aspects of the query’s design.

Reply | Quote

Hi Hans,

Thanks for your input. Your approach seems way too time consuming, though. I mean, supposing you have to modify a query, you’d have to go back to the Word document and change it there too. SQL Server is more flexible in that it lets you save your queries, comments included, in .txt or .sql (plain text files anyway). You write your code and it stays there without requiring you to remember whether you updated your Word document or not. I’m not sure I have the time or patience to go saving changes all day long, but it’s one of the best alternatives I’ve got so far.

Reply | Quote

A lot of the queries I use are called from a control on a form. I use the code behind the control to document the query, and usually put the query in a variable (strSQL) in the code. This works well for me especially when I have to go back to a project after an extended period. This doesn’t work for queries that feed reports, etc. but does work for queries that have other destinations (exporting data, manipulating data, etc.)

I have also used the table approach of storing the query name, the sql, a description and a date field for last updated. But you are right in stating that it takes more time.

Reply | Quote

Queries in Access do have a description property, which I’ve used in the past to enhance the information provided by a query name. If you want to keep the information in the database, it’s either that or a table that holds the query name and description. I’ve built apps in the past (pre-2000 versions) where I stored the query name, description and even SQL in a table to use as templates in creating new databases.

Reply | Quote

Hi Charlotte,

Yes, I’ve been getting by using the queries Description property. One difficulty I find with them is that in the rush of the day I tend to copy | paste queries and then edit the newly created one without noticing that the Description remains the same. Most of the times I succeed in correcting them, but unavoidably I’ll fail to correct all of them.

It’s so irritating that MS hasn’t included a simple code comment feature!

I wonder how hard it would be to create a simple app that strips all comments from a .txt file. It can probably be done with a powerful text replacer. I’ll be investigating this alternative and keep you posted if I find something worthwhile.

Reply | Quote