Can you tell what forms or reports use what queries, forms, etc?

[flugh]

Greetings! I'll admit to not searching for an answer before asking right up front. I'll search a bit later, but it's my day off and am hoping to have a couple pointers in the right direction when I go in the office tomorrow morning for work

I'm wondering if there's a programmatic way to determine if my queries and forms are used by another form, a report, etc? The collection of queries and forms I have now is growing, and the cruft is building up. As exciting and challenging as the old "delete it and hope for the best" method can be, it's not how I want to treat a production, mission-critical database ;-)

I'm using Access 2000 on Windows XP Home. My skill level is about a 4 out of 10 (which means I can grasp most concepts if you just say them loudly and s-l-o-w-l-y ;-)). Any tips would be gratefully appreciated, and good natured flames accepted with a sheepish grin.

----
JBR

 

[SteveR77]

JBR,

This might be a bit time consuming but under tools you can use the Analyze option and select the database documentor.

If you then select all your forms and reports you can look at the record source propery for each of them and that will show you the tables and queries that are used being used.

The only issue is if there are old tables and queries that are used by forms and reports that are not needed in your database there will be no way of accounting for that fact.


HTH,

Steve

 

[flugh]

Hey, that's interesting. Never have used that feature. Not sure if it will get me what I want, but definitely useful for other stuff!

I'm doing the big no-no and taking my home copy of the database and working on a Saturday night trying to tidy up. Ugh, I need a life! ;-)

----
JBR

 

[SteveR77]

JBR,


Good luck. Scrubbing inherited DBs can be a nightmare.


Steve

 

[flugh]

I wish I could blame this monster on someone else, but it's mine ;-)

I didn't know SQUAT about Access or any kind of database design when this thing started a couple years ago. Now I see that there's a lot to be said about some formal education and a good session with the boss and a whiteboard laying this kind of stuff out in advance.

The database is about to enter a new generation, just trying to evolve it accordingly. I should have gone to school for this stuff. It's sort of fun

----
JBR

 

[Crowley16]

if you want to do this programatically, then u can use the forms or queries objects in vba.

just loop through them all, and for each one, output it's recordsource property to somewhere...

I think this can be done, but I've never actually tried anything like this myself...

 

[MichaelRed]

Unfortunatly, some of what has preceeded this is not quite complete. It is possible (and occassionally encouraged) to use a query as the "ControlSource" (or other properties) of controls. So, to go one step further, you need to check some of the forms & reports control properties. Another "issue" is that queries may be "layered", so it MAY not appear as the source of any user interface object (Form or Control), but still be quite necessary for the overall application functionallity. Devling much deeper than this will make the average practicioner of (early learning curve) Ms. A. generally results in either abandoning the "clean up" effort or finding some mechanisim to get one of the several took sets which trace down the FULL usage of all objects (such as SpeedFerret or ?MzTools?).

Using the Search engine here on the site may find either (or both of these).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places