Query System Objects and Description

[psulions83]

Hi,

Is there a way to add the description from each item in the below queries to return the description from the properties menu for each item?

[B]Queries[/B][COLOR=#000000][FONT=Verdana]:[/FONT][/COLOR][INDENT]SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)=5 ORDER BY MSysObjects.Name;[/INDENT][COLOR=#000000][FONT=Verdana][B]Tables[/B]:[/FONT][/COLOR]
[INDENT]SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;[/INDENT][COLOR=#000000][FONT=Verdana][B]Reports[/B]:[/FONT][/COLOR]
[INDENT]SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;[/INDENT][COLOR=#000000][FONT=Verdana][B]Modules[/B]:[/FONT][/COLOR]
[INDENT]SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)= -32761 ORDER BY MSysObjects.Name;[/INDENT][COLOR=#000000][FONT=Verdana][B]Macros[/B]:[/FONT][/COLOR]
[INDENT]SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND  [/INDENT][COLOR=#000000][FONT=Verdana](MSysObjects.Type)= -32766 ORDER BY MSysObjects.Name;[/FONT][/COLOR]

 

[psulions83]

I never added reports to my original. I tried the below to no avail.

Public Function ReportDescr(stReportName As String) As StringOn Error Resume Next
ReportDescr = CurrentDb.ReportDefs(stReportName).Properties("Description").Value
End Function

SELECT MSysObjects.Name, ReportDescr([Name]) AS Description, 'Report Objects' AS Object_Order
	     FROM MsysObjects
	    WHERE (Left$([Name],1)<>"~") 
	      AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=	-32764

 

[Micron]

how did you get from this syntax

ModuleDesc = CurrentDb[COLOR=#ff0000][B].Containers[/B][/COLOR]("Scripts").Documents(stModuleName).Properties("Description")

to this

ReportDescr = CurrentDb.[COLOR=#ff0000][B]ReportDefs[/B][/COLOR][B][COLOR=#ff0000](stReportName)[/COLOR][/B].Properties("Description").Value

Try

ReportDescr = CurrentDb.Containers("reports").Documents(stReportName).Properties("Description")

A hint for future: save then compile your code. If something is a problem (like ReportDefs not being a member or property of the CurrentDb), it should bark at you. If you compile BEFORE saving you run the risk of the db crashing and you losing unsaved work. Will only have to happen once or twice and you'll become a convert of the first method.

 

[Micron]

BTW, I would expect that for any of these object functions/queries you have, if the object does not have a description value and you are running the query as a saved query, you will generate an error? Might be OK if the sql is being run in code, but methinks not if the query is run as a saved query. You are probably doing it in code only, but I thought I'd throw that out there just in case.

EDIT: never mind; I went back and checked because I seemed to recall that in my testing db I had recently turned on "break on all errors" in order to trap someone else's problem. That turned out to be the case. Your Resume Next took care of any errors OK; I thought it was breaking because I was running a saved query.