Solved Retrieving the Description property of query (1 Viewer)

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
I am trying to obtain the descriptive text I entered against the queries I have developed, as I want these to appear in documentation. This text doesn't appear in the the output from the database documenter.

The particular line to retrieve this information (and write it into a table specifically for the name, SQL and Description) is:

rstList!Description = CurrentDb.QueryDefs(i).Properties.["Description"]

When commented out the code produces results (although there does appear to be some clean up to be done)
When included to results in : Compile Error: Method or data member not found".

I have tried various changes to the way in which Description is referenced but to no avail - what is the correct syntax to obtain the description?

And btw:
this line
rstList!QueryName = CurrentDb.QueryDefs(i).Name

produces records that have the structure: ~sq_cfNonMbrPerson~sq_cfrmMember2sub
as well as records for the regular queries. Presumably arising from forms/ subforms using the query

Thanks for any assistance
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:15
Joined
Jan 20, 2009
Messages
12,849
I don't know about Description but some properties are not available as members of the object in the usual dotted format.
Try:
Code:
Currentdb.QueryDefs(i).Properties("Description")

Also note that some properties don't exist and can't be enumerated unless they have something in them. You may get an error about the property not existing for some objects if they don't have a Description. BTW If you want to write to them with VBA you have to create them first.

this line
rstList!QueryName = CurrentDb.QueryDefs(i).Name

produces records that have the structure: ~sq_cfNonMbrPerson~sq_cfrmMember2sub
as well as records for the regular queries. Presumably arising from forms/ subforms using the query

Yes. SQL used in things like RowSources for combos also have names like this.
 

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
I don't know about Description but some properties are not available as members of the object in the usual dotted format.
Try:
Code:
Currentdb.QueryDefs(i).Properties("Description")

Also note that some properties don't exist and can't be enumerated unless they have something in them. You may get an error about the property not existing for some objects if they don't have a Description. BTW If you want to write to them with VBA you have to create them first.

Yes. SQL used in things like RowSources for combos also have names like this.

Thanks - tried the suggestion and received a "3270, Property Not Found" - so while the code to retrieve the name and SQL parts of the query worked the table/report did not have any content for the description - I have entered a description for each query, however not for those for combos and within forms. Would it therefore be a matter of somehow excluding these - possibly by the name beginning with "~"
 

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
I have also tried:
Code:
                qName = rstList!QueryName
                rstList!qDescription = CurrentDb.QueryDefs("qName").Properties("Description")
which results in
Error 3265 - Item cannot be found in the collection
- not sure where to find it - must be somewhere
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:15
Joined
Jan 23, 2006
Messages
15,364
Show us some sample data where you have assigned a description to a query.
Here's a routine to print query name and description for querydefs in currentdb where a description exists.
As Galaxiom said. if there is no description present, then it seems Access says the Property doesn't exist. The ~prefix often indicates a deleted but not purged and/or temporary querydef. I'm sure there is a M$oft definition for what this (~prefix) represents, but I haven't found it at the moment.

Code:
' ----------------------------------------------------------------
' Procedure Name: testQryDesc
' Purpose: List queries - names and decriptions where a description exists.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 12-Jun-20
' ----------------------------------------------------------------
Sub testQryDesc()
10        On Error Resume Next
          Dim db As DAO.Database
          Dim i As Integer
20        Set db = CurrentDb
30        For i = 0 To db.QueryDefs.Count - 1
40            Debug.Print i, db.QueryDefs(i).name & Space(40 - Len(db.QueryDefs(i).name)) _
                          & db.QueryDefs(i).Properties("Description")
50        Next i
End Sub

Sample output:

266 GetCountOfAttachmentsinTable test table with an attachment field
267 GetFieldsForLabels Customers' names, addresses, and phone numbers.
275 GPierce9qry Part of http://www.accessforums.net/showthread.php?t=69841
 

Attachments

  • QryDescDemo.PNG
    QryDescDemo.PNG
    15.8 KB · Views: 105

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
Thanks - I'll take a look at the code and try adapting. As you requested here is firstly a screen shot showing the description recorded against a query that I want to retrieve:
1591962977610.png

Each query has a description recorded in the General tab, not against the field properties in the query
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:15
Joined
Jan 23, 2006
Messages
15,364
I think that if you save that query def, then the Description will also show in the Object Properties box.
 

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
The code is now running and producing a report output with descriptions. At the end a message shows "finished" and then throws up an error 49 Bad DLL Calling convention - a separate issue - this seemed to go away after adding DoCmd.RunSQL code to delete all the previously generated records in the table, before adding those generated by the loop.

The main changes made to the code were to Set db as CurrentDb, rather than use CurrentDb, then I also used the Print Debug line:
Code:
rstList!qDescription = db.QueryDefs(i).Properties("Description")
Debug.Print i, db.QueryDefs(i).Properties("Description")

I still cannot see any effective difference in the code as listed in the second post which produced an error ... however happy to get the output.

Thanks so much - I am a new to this - (retired) and using my COVID time to develop a management application for a Charity
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:15
Joined
Jan 23, 2006
Messages
15,364
So it appears you are populating a table rstList!qDescription with the query description.
Glad it's working.
 

Isaac

Lifelong Learner
Local time
Today, 07:15
Joined
Mar 14, 2017
Messages
8,738
qName = rstList!QueryName rstList!qDescription = CurrentDb.QueryDefs("qName").Properties("Description")
Just to answer this specifically … the problem with your code in this case is having quotation marks around qName. Remove those quotes.

It might be much easier in the long run to just use a table driven way of storing this information. Just my 2 cents.
 

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
For reference the code to write the info to the table is:
Code:
Public Function ListQueries()

On Error Resume Next
Dim db As DAO.Database
Dim rstList As DAO.Recordset
Dim i As Integer
Dim QueryName, qName, strqryDel As String

strqryDel = "DELETE T001SQLCollection.* FROM T001SQLCollection;"
Set db = CurrentDb

DoCmd.RunSQL strqryDel
 
        For i = 0 To db.QueryDefs.Count - 1
        Set rstList = db.OpenRecordset("T001SQLCollection")
            With rstList
               .AddNew
                rstList!QueryName = db.QueryDefs(i).Name
                rstList!qSQL = db.QueryDefs(i).SQL
                qName = rstList!QueryName
                rstList!qDescription = db.QueryDefs(i).Properties("Description")
                Debug.Print i, db.QueryDefs(i).Properties("Description")
                rstList.Update
            End With
    Next i
    rstList.Close
 
MsgBox "Finished"
 
End Function

it is called when a report is run - to help produce doc of the project. Thank you
 

GaP42

Active member
Local time
Tomorrow, 01:15
Joined
Apr 27, 2020
Messages
311
`And the query for the report looks at the name of the query to classify and present the name:
QryClass:
IIf(Left([QueryName],5)="~sq_c","subForm",
IIf(Left([QueryName],5)="~sq_d","subReport",
IIf(Left([QueryName],5)="~sq_f","Form",
IIf(Left([QueryName],5)="~sq_r","Report","Query"))))
QName:
IIf([QryClass]="SubForm",Replace([QueryName],"~sq_c","SubForm: ",1,2),
IIf([QryClass]="SubReport",Replace([QueryName],"~sq_d","SubReport: ",1,2),
IIf([QryClass]="Form",Replace([QueryName],"~sq_f","Form: ",1,1),
IIf([QryClass]="Report",Replace([QueryName],"~sq_r","Report: ",1,1),"Query: "+[QueryName]))))
 

Users who are viewing this thread

Top Bottom