How to print a simple list of tables, queries & forms ?

[Mel]

How can I print a list of all the tables, queries and forms in my data
base?

I've tried Tools | Analyze | Documenter | Tables | Options | Select
All |
... Include for Table = Nothing
... Include for Fields = Nothing
... Include for Indexes = Nothing

We only want a simple list of tables, queries & forms.

thanks for any help.

Mel

 

[Susie DBA [MSFT]]

Dim o As AccessObject
For each o in currentData.AllTables
debug.print o.Name
Next o

For each o in currentProject.AllForms
debug.print o.Name
Next o

For each o in currentProject.AllQueries
debug.print o.Name
Next o

 

[Jeff Conrad [MSFT]]

Hi Mel,

A couple of options for you:

http://www.accessmvp.com/djsteele/Documentor.html

http://www.accessmvp.com/JConrad/accessjunkie/csdtools.html

Both utilities are free, easy to set up, and contain zero trans fats.

--
Jeff Conrad - Access Junkie - MVP Alumni
Software Development Engineer in Test
XAS Services
Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Guest]

SELECT
IIf([MSysObjects].[Type]=1,'TABLE',IIf([MSysObjects].[Type]=5,'QUERY',IIf([MSysObjects].[Type]=-32768,'FORM',IIf([MSysObjects].[Type]=-32764,'REPORT',IIf([MSysObjects].[Type]=-32766,'MACRO',IIf([MSysObjects].[Type]=-32761,'MODULE',IIf([MSysObjects].[Type]=-32756,'PAGE',IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',IIf([MSysObjects].[Type]=6,'TABLE
LINKED','Unknown'))))))))) AS ObjectType, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like
"Msys*") AND ((MSysObjects.Type) Not In (2,3)))
ORDER BY
IIf([MSysObjects].[Type]=1,'TABLE',IIf([MSysObjects].[Type]=5,'QUERY',IIf([MSysObjects].[Type]=-32768,'FORM',IIf([MSysObjects].[Type]=-32764,'REPORT',IIf([MSysObjects].[Type]=-32766,'MACRO',IIf([MSysObjects].[Type]=-32761,'MODULE',IIf([MSysObjects].[Type]=-32756,'PAGE',IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',IIf([MSysObjects].[Type]=6,'TABLE
LINKED','Unknown'))))))))), MSysObjects.Name;

 

[fredg]

How can I print a list of all the tables, queries and forms in my data
base?

I've tried Tools | Analyze | Documenter | Tables | Options | Select
All |
.. Include for Table = Nothing
.. Include for Fields = Nothing
.. Include for Indexes = Nothing

We only want a simple list of tables, queries & forms.

thanks for any help.

Mel

No Reports?

Create a new query using this SQL:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4))<>"MSys")) OR
(((MSysObjects.Type)=5) AND ((Left([Name],1))<>"~")) OR
(((MSysObjects.Type)=-32768))
ORDER BY MSysObjects.Type, MSysObjects.Name;


In Query Design View, make sure the Name and Type Show check boxes are
checked.

Create a new report using the above query as it's recordsource.
In the Report Design View click on View + Sorting and Grouping.
Set up a group header on [Type], then add a sort on [Name].

In the Type Group Header section add an unbound text control:

=IIf([Type]=-32768,"Forms",IIf([Type]=1,"Tables","Queries"))

Make sure the name of this control is NOT "Type".

Include the Name field in the Detail section.
Run the report.

 

[Mel]

Susie,

I tried your code as a 'standard module'

I get an error on the lase section..."Object doesn't support this
property or method"

Debug highlights this line...
For each o in currentProject.AllQueries

I even tried typing the line... and see that after currentProject.
the dropdown doesn't show AllQueries as an option.
of course it doesn't show AllTables either ???!!!

Advice on this?

And, it prints in the "debug window"
Are you suggesting I copy that, paste it in Notepad and Print to paper
from there?

thanks for the help.

Mel

 

[Tony Toews [MVP]]

Dim o As AccessObject
For each o in currentData.AllTables
debug.print o.Name
Next o

For each o in currentProject.AllForms
debug.print o.Name
Next o

For each o in currentProject.AllQueries
debug.print o.Name
Next o

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Aaron Kempf]

note that Tony is niether MOST, VALUABLE -OR- A PROFESSIONAL

he is a MDB cry baby

 

[Aaron Kempf]

sorry it might be CURRENTDATE.AllTables

sorry I don't use MDB at all for anything

 

[Susie DBA [MSFT]]

god that code is just ridiculous

yet another reason you should use SQL Server-- it doesn't make you
remember obscure integer values


C = Check constraint
D = Default (constraint or stand-alone)
P = SQL stored procedure
FN = SQL scalar function
R = Rule
RF = Replication filter procedure
TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either
the database or server scope)
IF = SQL inline table-valued function
TF = SQL table-valued function
V = View

SELECT
IIf([MSysObjects].[Type]=1,'TABLE',IIf([MSysObjects].[Type]=5,'QUERY',IIf([­MSysObjects].[Type]=-32768,'FORM',IIf([MSysObjects].[Type]=-32764,'REPORT',­IIf([MSysObjects].[Type]=-32766,'MACRO',IIf([MSysObjects]..[Type]=-32761,'MO­DULE',IIf([MSysObjects].[Type]=-32756,'PAGE',IIf([MSysObjects].[Type]=8,'RE­LATIONSHIPS',IIf([MSysObjects].[Type]=6,'TABLE
LINKED','Unknown'))))))))) AS ObjectType, MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like
"Msys*") AND ((MSysObjects.Type) Not In (2,3)))
ORDER BY
IIf([MSysObjects].[Type]=1,'TABLE',IIf([MSysObjects].[Type]=5,'QUERY',IIf([­MSysObjects].[Type]=-32768,'FORM',IIf([MSysObjects].[Type]=-32764,'REPORT',­IIf([MSysObjects].[Type]=-32766,'MACRO',IIf([MSysObjects]..[Type]=-32761,'MO­DULE',IIf([MSysObjects].[Type]=-32756,'PAGE',IIf([MSysObjects].[Type]=8,'RE­LATIONSHIPS',IIf([MSysObjects].[Type]=6,'TABLE
LINKED','Unknown'))))))))), MSysObjects.Name;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

How can I print a list of all the tables, queries and forms in my data
base?

I've tried Tools | Analyze | Documenter | Tables | Options | Select
All |
... Include for Table = Nothing
... Include for Fields = Nothing
... Include for Indexes = Nothing

We only want a simple list of tables, queries & forms.

thanks for any help.

Mel- Hide quoted text -

- Show quoted text -