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]

 

[stumac]

AFAIK You cant get the properties without using a User defined function. If you insert the following function into a model and then call from a query it should work:

Public Function TableDescr(stTableName As String) As String
On Error Resume Next
TableDescr = CurrentDb.TableDefs(stTableName).Properties("Description").Value
End Function

call as follows:

[FONT=Verdana][B]Tables[/B]:[/FONT]SELECT MSysObjects.Name, [COLOR=#ff0000]TableDescr([Name]) AS Description[/COLOR]  FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND 
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;

 

[psulions83]

Stumac,

Thank you... this worked for the tables. Can this be modified to call the same things for queries and macros?

 

[psulions83]

The first worked but I am not sure if you can do the same thing with Macros? I tried running with a S and no S and got same results in Macro(s).

Public Function QueryDescr(stQueryName As String) As StringOn Error Resume Next
QueryDescr = CurrentDb.QueryDefs(stQueryName).Properties("Description").Value
End Function

SELECT MSysObjects.Name, QueryDescr([Name]) AS Description
FROM MsysObjects 
WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)=5 
ORDER BY MSysObjects.Name;






Public Function MacroDescr(stMacroName As String) As String
On Error Resume Next
MacroDescr = CurrentDb.MacroDefs(stMacroName).Properties("Description").Value
End Function

SELECT MSysObjects.Name, MacroDescr([Name]) AS Description
FROM MsysObjects 
WHERE (Left$([Name],1)<>"~") AND 
(MSysObjects.Type)=-32766
ORDER BY MSysObjects.Name;

 

[stumac]

The following should work for Macros:

Public Function MacroDesc(stMacroName As String)
On Error Resume Next
MacroDesc = CurrentDb.Containers("Scripts").Documents(stMacroName).Properties("Description")
End Function

 

[psulions83]

Thanks that works. Is there a specific part missing below for modules? I get the query to come back but it doesnt have the description.

Public Function ModuleDesc(stModuleName As String)On Error Resume Next
ModuleDesc = CurrentDb.Containers("Scripts").Documents(stModuleName).Properties("Description")
End Function

SELECT MSysObjects.Name, ModuleDesc([Name]) AS Description
FROM MsysObjects
WHERE (Left$([Name],1)<>"~") AND
(Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=-32761
ORDER BY MSysObjects.Name;

 

[stumac]

You are looking at Macros modules would not show under scripts.

change Scripts to modules:

ModuleDesc = CurrentDb.Containers("Modules").Documents(stModuleName).Properties("Description")

 

[psulions83]

that worked....thank you for your help!

 

[stumac]

No worries - apologies, I had only seen the first part of your original Question when I answered, could have saved a lot of going back in forward if I had paid more attention!

 

[psulions83]

Ahhh no worries. The help is all that matters.