create table of query names and descriptions

[Melissa]

I am trying to document what my queries are doing in my database by using the
descriptions. I have found through reading other postings that I needed to
unhide the hidden tables in order to create a query that makes a table with a
list of objects. Is there a hidden table that includes the query description
so that I can add that to my table?

Documenter gives me too much information in a format that is not easily
manipulated in word or excel. And I can't download any add-ins on my
workstation.

Please help!

 

[Gina Whipp]

There is no table that I know of but I found this a long time ago and never
used for anything but it does work. Presently the information will post to
the Immediate Window (Crtl+G while in the Modules window).

Function EditQueryDescriptions()
'Posted by Dennis Ruppert 2005
On Error GoTo Err_EditQueryDescriptions

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim QueryNameText As String

Set db = CurrentDb

For Each qry In db.QueryDefs
NoDescription = False
Set prp = qry.Properties("description")
If NoDescription Then
Debug.Print "Query: " & qry.Name
DescriptionText = "No Description"
QueryNameText = qry.Name

Else
Debug.Print "Query: " & qry.Name & " - " & prp.Value
DescriptionText = prp.Value
QueryNameText = qry.Name

End If
Next

Exit_EditQueryDescriptions:
db.Close
Exit Function

Err_EditQueryDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditQueryDescriptions
End If
End Function

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Clifford Bass]

Hi Melissa,

You could do this: In a normal module (not form, report or class)
place this code:

Public Function QueryDescription(ByVal strQueryName As String) As String

On Error GoTo Handle_Error

QueryDescription =
CurrentDb.QueryDefs(strQueryName).Properties("Description")

Exit_Function:
Exit Function

Handle_Error:
Select Case Err.Number
Case 3265
' Query does not exist
QueryDescription = "Error! Query """ & strQueryName & """ not
found."

Case 3270
' No description
QueryDescription = vbNullString

Case Else
QueryDescription = "Error! " & Err.Number & ": " &
Err.Description

End Select
Resume Exit_Function

End Function

Then use something like this for your query:

SELECT MSysObjects.Name, QueryDescription([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Type)=5));

Hope this helps,

Clifford Bass

 

[Melissa]

I'm not a macro user, so I don't know what I'm doing, but it gave me exactly
what I needed. I'm able to copy it to word and that will be very useful.
--
Thank you!
Melissa

There is no table that I know of but I found this a long time ago and never
used for anything but it does work. Presently the information will post to
the Immediate Window (Crtl+G while in the Modules window).

Function EditQueryDescriptions()
'Posted by Dennis Ruppert 2005
On Error GoTo Err_EditQueryDescriptions

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim prp As DAO.Property
Dim NoDescription As Boolean
Dim DescriptionText As String
Dim QueryNameText As String

Set db = CurrentDb

For Each qry In db.QueryDefs
NoDescription = False
Set prp = qry.Properties("description")
If NoDescription Then
Debug.Print "Query: " & qry.Name
DescriptionText = "No Description"
QueryNameText = qry.Name

Else
Debug.Print "Query: " & qry.Name & " - " & prp.Value
DescriptionText = prp.Value
QueryNameText = qry.Name

End If
Next

Exit_EditQueryDescriptions:
db.Close
Exit Function

Err_EditQueryDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_EditQueryDescriptions
End If
End Function

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index.htm

 

[Gina Whipp]

Melissa,

As long as you get what you, that is what counts. Later in your spare time,
<G>, you can study the code.

Your welcome
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I'm not a macro user, so I don't know what I'm doing, but it gave me
exactly
what I needed. I'm able to copy it to word and that will be very useful.

 

[a a r o n . k e m p f]

uh, you can make a query to do this

select name from msysobjects where name like 'qry*'

 

[Clifford Bass]

Hi Aaron,

She could. But it would only work if she starts the names all of her
queries with "qry" and does not start anything else with that. Better to
check the Type column (5 = query). Plus, you cannot get the description from
that table. I think it is hidden inside of one of the binary data columns.
Or maybe located in some other table.

Clifford Bass

uh, you can make a query to do this

select name from msysobjects where name like 'qry*'

 

[a a r o n . k e m p f]

yah, it still demonstrates that where the majority of people would
rely on DAO to do something-- it's just not necessary

DAO is dead and it has been for a decade.
Only a complete loser would rely on a library that forces you to de-
instantiate every object you make.
It defeats the whole purpose of 'Visual Basic'.

-Aaron

 

[IP-PC]

yah, it still demonstrates that where the majority of people would
rely on DAO to do something-- it's just not necessary

DAO is dead and it has been for a decade.
Only a complete loser would rely on a library that forces you to de-
instantiate every object you make.
It defeats the whole purpose of 'Visual Basic'.

-Aaron