Printing a list of object dependencies

[domino3]

I use access 2003, and need to print off the list of objects that depend on a table in my database. I can get the list to appear on screen, but cannot find any way of printing it. Can anyone let me know how to do it, if it is possible?

Thank you.

 

[Darrylles]

Hi dom,

Come on mate - give us some detail:

HOW are you getting the data to the screen?

If you can see everything that you want to print on the screen, then a sure-fire is : CTRL + Print Screen button.

Open MS Paint - press Shift + Insert button - hey presto.

Otherwise - explain how you get data to display.


ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[MichaelRed]

I'd just like to know how you get "ALL" of the dependencies for an object ... and to know that it is truly getting "ALL" of them. e.g. If query a references the table and query B references query A, does you list include both?



MichaelRed

 

[domino3]

Sorry I've taken a while to respond, but I got caught up with other problems at work. The result I am trying to achieve is a list of object dependencies I can sort or work with which I can save in word or excel or some other package. However, when I tried using the screen dump I couldn't find a way of converting the picture of the screen with the data on it which I pasted in to paint into a workable list.

I hope that's clearer.

And I don't know whether I have all the dependencies. I'm just going to have to trust the system as and when I get the list I need working.

Thank you.

 

[MajP]

By "object dependencies" do you mean table relationships. If so you may want to look at the system tables. "MSysRelationships" "MSysObjects" in particular. You can build a query to show the needed data.

 

[domino3]

I would like a list of all the queries, forms, reports, macros, modules and relationships attached to one particular table. I've been asked by my users to combine the data on two tables and to convert all the existing queries, etc to make sure they all work after the change. So I want a complete list to make sure that I don't miss anything. I've just had a look in "MSysRelationships" and "MSysObjects" and am not sure how I would use the data in them to create the list I want.

 

[MajP]

Would the Documenter suffice?
"Tools>Analyze>Documenter

 

[domino3]

Unless I am using it incorrectly, documenter doesn't give a list of all the queries, forms etc that a table is linked to. Object dependencies seem to be the only way to get the list I'm after.

 

[MichaelRed]

only to veer off the beaten track (again), why are "USERS" dictating the structure of the database?

continuing the theme ... does the combining contribute to the DEnormalization of the whole? will there be fields in the combined table which are always empty under some condition(s)?

finally, why no response to my previous post? I really AM interested.



MichaelRed

 

[MajP]

I use A2002 and did not even know that there was an "Object Dependency" window (2003 feature), so I do not know about using it. But I think you could easily make a tabular report doing some queries on the System tables. I was able to get a lot by just some short playing around. I did it as a bunch of small queries which you could dump into a report using subreports:

1) get the tables qryTables:
SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.ParentId, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.ParentId)=251658241) AND ((MSysObjects.Type)=1))
ORDER BY MSysObjects.Name;

2) get the forms qryForms
SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.ParentId, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768))
ORDER BY MSysObjects.Name;

3) get the Modules qryModules
SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.ParentId, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32761))
ORDER BY MSysObjects.Name;

etc.

4) Defined queries not queries on a form, report, or control qryQueries

SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.ParentId, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~Sq*") AND ((MSysObjects.ParentId)=251658241) AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

5) Queries on a form qryFormQueries:
SELECT MSysObjects.Id, MSysObjects.Name, MSysObjects.ParentId, MSysObjects.Type, MSysObjects.Database
FROM MSysObjects
WHERE (((MSysObjects.Name) Like "~sq_f*") AND ((MSysObjects.ParentId)=251658241) AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

You got the idea? Do the same stuff for forms and relationships

So lets say I want to see all the defined queries related to a table grouped by table:

6)SELECT MSysQueries.Name1, qryQueries.Name
FROM qryQueries INNER JOIN MSysQueries ON qryQueries.Id = MSysQueries.ObjectId
GROUP BY MSysQueries.Name1, qryQueries.Name
HAVING ((Not (MSysQueries.Name1) Is Null And (MSysQueries.Name1) Not Like "MSys*"))
ORDER BY MSysQueries.Name1, qryQueries.Name;

7)All form queries related to a Form qryForm_Queries

SELECT qryForms.Name, qryFormQueries.Name, qryFormQueries.Id
FROM qryFormQueries, qryForms
WHERE (((InStr([qryFormQueries].[Name],[qryForms].[Name]))<>0));

8) All tables and forms grouped by table
SELECT MSysQueries.Name1, qryForm_Queries.qryForms.Name, qryForm_Queries.qryFormQueries.Name
FROM qryForm_Queries INNER JOIN MSysQueries ON qryForm_Queries.Id = MSysQueries.ObjectId
WHERE ((Not (MSysQueries.Name1) Is Null));


This is a general idea of how I would do it.

Like Michael said this will not get "All", but enough of the high level information, especially queries based on queries.

You can also do some vba using the "currentproject" object and the related collections (allForms,allmodules..allreports).

 

[MichaelRed]

Well, the originator seems to have left the house?

MajP, you can get a bit further (and faster?) bu including the MSysQueries. There are some threads here (in Tek-Tips) and elsewhere which help decoding the somewhat arcane topology of this one, but after all is said and done I have yet to get all the way through to being sure that the trail is complete. Big hang up for me it tracking the alaiases, which is a particular problem with nested aggregate queries. If you see a path through this part of the maze, please feel free to upgrade my education and respect for you abilities.




MichaelRed