LIsting Query Description

[Guest]

I can debug.print a list of Query names, no problem. But what I really want
is a list of the query decriptions I have added in. Since Description is not
one of the optons I see on the list, anyone have a method I could use?

Thanks,

Janie

 

[Guest]

Janie,

The following code should do what you ask. It was originally written by
Brendan Reynolds (MVP). It does more than you need but you should be able to
extract what you need from it.

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


Daniel