Finding Access object dependencies

[DarrenD]

Is there a third-party add-on or a VBA solution that will show me the depenencies
between objects in Access 97. E.g., what macros and modules are used within a
form, what macros are unused, etc.

 

[Durkin]

If you identify all the places that objects can be used you could write a routine that would go through all the other objects in the database checking for references to each object. I've just re-read that sentence and it sounds like gibberish. X-) What I mean is that, for instance, if you wanted to find all places where a table is used your routine would do the following.

1. Look at the Sql in each query in the database and if the tablename appeared there, record the query's name.
2. Look at the recordsource for each form and see if the table name is included there. Also, if you want to, check the recordsource for any of the queries returned by No. 1.
3. Check the rowsource of any combo boxes in each of the forms.
4. Check each reports' recordsource and any controls on it.
5. Check each module in the database, including form modules.

I've never actually written a macro so I don't know how to check these but I'm sure there's a way. You can output the results to the debug window or to a text file.
Do similar checks for all objects in the database. It might take a little while to run but if you successfully think of all the places objects can be used it should do what you want. Durkin
alandurkin@bigpond.com

 

[MichaelRed]

Alan has the right 'approach'. This has been discussed in this forum previoiusly, I believe within the last three months. As discussed, you need to basically use the documents collection to obtain the lists of everthing. Itterate through these lists, basically logging everthing except the keywords, comments, and procedure local variables. Everthing else is a varialble, so then just build a cross reference. You do need to exercise some 'extreme' care with queries, as they are where many variables get created - this isespecially true of the crosstab queries, as the column headings are not determined/available directly from the SQL, but may (sometimes) be obtained from the "Headings" property. In many instances, this is not a real problem, as the dataconsumer of the crosstab query will list the pivot fields WHICH ARE USED, you just loose the link through the crosstab query itself.

You should do a search through the Ms. Access forums on the subject. Use SEVERAL keywords, such as cross reference, coorelation ...




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over