List of Queries and Descriptions

[wandan]

Is there an easy way to get a list of all queries with the query descriptions out of an Access database? I found the MySysObj table, so I see that I can get the list of queries. However, I would also like to pull in the description.

Thanks.

 

[kjv1611]

Howabout using the QueryDefs collection?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[wandan]

Can you give me an example?

 

[kjv1611]

Well, just for instance, you could do this:

Enter this code in a module, not form window.. to do this, just go to the Database Window (F11 in Access), select "Modules" group, and create a new module - if you don't already have one created. Once in the VBA Window, enter this code:

Private Sub ListQueryNames()
  Dim qryDef as DAO.QueryDef
  Dim db as DAO.Database
  Set db = CurrentDb
  For Each qryDef in db.QueryDefs
    Debug.Print qryDef.Name
  Next
  Set qryDef = Nothing
  Set db = Nothing
  qryDef.Close
  db.Close
End Sub

If you just copy and paste this code to your VBA module window, it should work. In order to see what prints out with debug.print, just hit [ctrl] + G. That will show the "Immediate" window which shows debug.print values at the bottom of the VBA window..

See if that gets you going.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[wandan]

Close, but I also need the be able to get to the "Description" property for the query. Any suggestions there?

 

[kjv1611]

Hmm, not sure, never tried, but it may be in the QueryDef(s) collections.. try searching the help file to see if it exists. If it does, it should be as simple as changing .Name to .Description.

You could also try just editing the code, like this:
After the qryDef in any location, hit . and scroll through the options that come up that could be used there..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[wandan]

Thanks for the help. I have been playing with it, and unfortunately, it is not as easy as adding .Description. I will just keep playing with it until I find the answer. Thanks again for the help.

 

[Zion7]

wandan,
is this what you mean by description, the SQL statement...

Sub QueryD()

Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
Debug.Print qdf.Name & ": " & vbCrLf & qdf.SQL
Next qdf

End Sub

If so, KJV, you were very close...

 

[PHV]

You may try this:
qdf.Properties("Description").Value
But you'll probably have to play with On Error Resume Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244