I took a 'low-tech' approach here, and wrote the info to a CSV file. Excel
can open a CSV file, and it was simpler than automating Excel to write
directly to a worksheet.
Something a little unexpected happened when I ran this code in Access 2003.
As the comments in the code indicate, I expected that a query that had never
had a Description defined would not have a Description property. Instead,
what happened is that while no description was listed for a newly created
single-table query in the database window, the code returned the description
of the table on which the query was based. If you run this code in an
earlier version of Access, I'd be interested to know if the same thing
happens in that version.
This is DAO code. If you don't already have a reference to the Microsoft DAO
3.6 Object Library (or 3.51 in Access 97) you'll need to add it (Tools,
References in the VBA editor).
Public Sub ListQueries()
Dim strFile As String
Dim intFile As Integer
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
Dim prps As DAO.Properties
Dim prp As DAO.Property
Dim strName As String
Dim strDescription As String
'Save file in same folder as Access app. This
'would need modification for Access 97.
strFile = CurrentProject.Path & "\Queries.csv"
intFile = FreeFile
'Change Append to Output if you want to overwrite
'any existing file with this name.
Open strFile For Append As intFile
Set db = CurrentDb
Set qdfs = db.QueryDefs
For Each qdf In qdfs
'Ignore temporary queries.
If Left$(qdf.Name, 4) <> "~sq_" Then
strName = qdf.Name
'Description is an extended property that does not
'exist if no value has ever been asigned to it. Avoid
'the error that would be raised by attempting to
'refer to a non-existant property.
strDescription = vbNullString
Set prps = qdf.Properties
For Each prp In prps
If prp.Name = "Description" Then
strDescription = prp.Value
Exit For
End If
Next prp
Write #intFile, strName, strDescription
End If
Next qdf
Close #intFile
MsgBox "Finished"
End Sub
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com
The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
