Under File menu - view and edit (ms access) database properties allows you to see a list of all the Objects in the database - Is there a way to export this list?

Question

Under File menu - "view adn edit database properties" I can see all of the objects within the d/b.  In this particular example; I have a d/b w/ ~ 100 tables and ~ 100 queries, there's a couple macros but there are no modules, forms or reports; the d/b is mostly used for staging lots of transactional data.

Is there a VBA routine or some other way to export or print this list ???

I came across the following SQL statement; but the output or data sheet view of this function loses the Object descriptor; e.g., Table, Form, Query, Macro, etc, etc.

SELECT MSysObjects.Name, MSysObjects.Type

FROM MSysObjects

WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5 Or (MSysObjects.Type)=-32768 Or (MSysObjects.Type)=-32764));

Thanks in advance for any ideas!!

Steve

Answer 1

actually, my predecessor did a good job of using a good naming convention for each d/b object so it's pretty easy to summarize using the sql statement above ... by using a t*** for tables and a q*** for queries

Count of Type	Type
Name	Sys Object or Table	Query
f_27EC230AE5614FB0946AA90CCCB04332_Data	1
Fueltrac detail report		1
MSysAccessStorage	1
MSysComplexType_Attachment	1
tWoodfordByDate	1
tWoodfordByPart	1
Grand Total	108	89

however, I'm still curious if there isn't a simpler way to use the file menu view properties to get an exported list of table of contents ...

thanks ,

Steve

Answer 2

Have you checked out the Database Documenter?

Answer 3

Yes, I did check that out but 1) it's really time consuming given all the objects in this d/b 2) it seems to list out a lot of extraneous details I don't need for this purpose, and 3) my d/b has a lot of linked tables residing on legacy mainframes and/or mapped network drives and half the time the system isn't able to locate those files due to poor drive mappings or a lack of the use of Universal Naming Conventions - so those items or objects get skipped any way ... thanks for the suggestion!

Have you checked out the Database Documenter?

Answer 4

Ran the d/b documenter again and it does produce more detail than I'm seeking at present; e.g., lists all Properties, User/Group permissions, and the SQL view of the code for queries which is helpful; but again I'm just looking for a high level summary of contents by object type.   A several page summary of contents by type as opposed to a couple hundred plus pages.

Is this possible?

Thanks,

Steve

Answer 5

One thing that really helps with the documenter is to open up the properties sheet and UN-check MOST options.

For example, you get “pages” of material for one table, but if you uncheck all options, say like this, then the resulting output is FAR more useful.

So you can “massive” reduce the volume of the documenter. And these days, I not really sure if you want to use paper or a PDF, as you can simply use the Application as the reference document anyway.

And don’t forget the dependences view. This gives you a tree view of all objects, and you can “drill down” the dependencies. So what forms, reports, tables and even quires are involved in a SINGLE query can be viewed.

This amazing (but little used) feature looks like this:

The above gives a GREAT “overall” view of objects in the database and does so via a fluid UI that allows one to inspect a given object, and dependences. And you can click to open + view the objects in design mode right from that treeview. In effect you can “surf” the whole project with dependences. And note how even a query based on a query etc. can be observed.

A “large” printout does little to document a single object and the “very” important dependences. I “much” agree with your point about creating large volumes of documentation does little to help your goal here.

The above "dependency" view is perhaps the best unknown tool in Access that gives the developer a “view” of the application. In fact I wish other development systems had the above feature!

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada