create list of table and query names 1

[jdttek]

I am looking for a easy way to create a list of table, query and form names that can be pasted into an excel spreadsheet. There are lists on the main database screen and in properties, but there is no way to cut and paste these names. Any easy solution? Or does such a list exist somewhere that I don't know about?

jdtttek

 

[SoloDroid]

Hello jdtttek,
Paste this into a VBA module, run it, and copy the results out of the immediate window:

Sub main()
Dim q As QueryDef, t As TableDef, l As Long
Debug.Print "Queries:"
Debug.Print "--------------------"
For Each q In CurrentDb.QueryDefs
    If Left$(q.Name, 1) <> &quot;~&quot; Then
        Debug.Print q.Name
    End If
Next q
Debug.Print &quot;--------------------&quot;
Debug.Print &quot;Tables:&quot;
Debug.Print &quot;--------------------&quot;
For Each t In CurrentDb.TableDefs
    If Left$(t.Name, 4) <> &quot;MSys&quot; Then
        Debug.Print t.Name
    End If
Next t
Debug.Print &quot;--------------------&quot;
Debug.Print &quot;Forms:&quot;
Debug.Print &quot;--------------------&quot;
For l = 0 To CurrentProject.AllForms.Count - 1
    Debug.Print CurrentProject.AllForms(l).Name
Next l
End Sub

Enjoy, Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°

 

[MichaelRed]

Look into the MSysObjects (System) table. It is a Hidden table, so you need to show hidden objects to "SEE" it in the db window, although you can access it via a standard select query w/o being able to see it. some (much?) of the info (fields) are not readily amenable to interpertation, however the object name is "plain text" and the type is easily discerned from ye olde mark 1 eyeball review technique. A SMALL ammount of effort removes the necessity of code and replaces it with a simple query.

Copy the following 'text' and paste it into the SQL view of a new query and "run" it. Review the resultset. It is rather crude, but will show the info you are asking for. Remove some (most) of the conditional (IIF and WHERE stuff) to see other attributes of the objects. Do NOT attempt to modify any of the information in this (or any system object).

As to the export of the information, you will, of course, do as you choose w/o regard to this advice, but the advice is given (no additional charge). DO NOT. The information (results set) is dynamic. There is every reason to NOT send it out to the rest of the world. Whatever others may want or use the information for should be amenable to a review as a report object from within Ms. A.

SELECT msysobjects.Name, msysobjects.Type, IIf([Type]=-32768,"Form") AS Forms, IIf([Type]=-32761,"Module") AS [Module], IIf([Type]=-32764,"Report") AS Report, IIf([Type]=1,"Table") AS [Table], IIf([Type]=5,"Query") AS Query
FROM msysobjects
WHERE (((msysobjects.Name) Not Like "MSys*" And (msysobjects.Name) Not Like "~*") AND ((msysobjects.Type)=1 Or (msysobjects.Type)=5 Or (msysobjects.Type)=-32768 Or (msysobjects.Type)=-32761 Or (msysobjects.Type)=-32764))
ORDER BY msysobjects.Type
WITH OWNERACCESS OPTION;

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TomCologne]

You could also try:
Tools>Analyze>Documenter
and export the returned report to Excel.

You're not alone,

TomCologne

 

[haneen97]

jdttek, Do you realize that you have my email as your contact email? I have getting all your replies. Please fix your profile.

Thanks

Mo

 

[haneen97]

This is a great tool; I have been looking fro something like this for long time. My solution was, to add standard abbreviations like (tbl, qry, frm, and rpt) in front of the object names and go after the name. Is there a way to make MichaelRed's statement work so you will get all the types in one column? The example below did not work, but is there something similar?

SELECT MSysObjects.Name, MSysObjects.Type,
IIf([Type]=-32768,"Form",-32761,"Module",-32764,"Report",1,"Table",5,"Query&quot AS Type_Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*"
And (MSysObjects.Name) Not Like "~*&quot
AND ((MSysObjects.Type)=1
Or (MSysObjects.Type)=5
Or (MSysObjects.Type)=-32768
Or (msysobjects.Type)=-32761
Or (MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Type
WITH OWNERACCESS OPTION;


Thanks

Mo

 

[MichaelRed]

In a devel envoiornment, I include a small procedure to return the "Name" associated with the "Type" field. The procedure is just a select case statement block which sets the procedure return to the name / string which identifies the object type -to my desired "word".

MichaelRed
m.red@att.net

Searching for employment in all the wrong places