MS Access 2007: Find Where a Form / Query / Report is Used

Question

History: I inherited a rather large app written using MS Access with lots of forms, queries, and reports. Because it looks like some of these items that were copied as a way of backing them up, I've got no idea if they are actually used anywhere.

Question: I am starting the process of cleaning up the app and need a way to find if and where forms, reports, or queries are used so that I know if I can delete or refactor them. Is there a good way have Access search the events of buttons for the names of forms / reports?

(Access' find feature seems to find only records unless I'm missing a setting)


Edit - Solutions:

1.) As has been mentioned in the answers and comments below, it would be a valuable lesson to rebuild the application by creating a fresh Access file then going form by form, starting with the login screen, and seeing what is missing. This would provide great insight into the whole application.

2.) I found this post that discusses using the "Database Documenter" to dump out all of the information relating to the objects, VBA, etc used in a given form. The resulting text file is easily searchable for the use of a single particular query, report, or form. It would not provide me with the same level of knowledge as re-building the whole application would, but it is a good stop-gap measure for targeted knowledge / possible cleanup.

Answer 1

Say you have a form named frmOne which has a command button with the code-behind as:

DoCmd.OpenReport "rptFoo"

And rptFoo uses qryFoo as its record source.

Enabling Track Name Autocorrect, then viewing the Object Dependencies for frmOne will not notify you that rptFoo is required by frmOne. It can however tell you qryFoo is required by rptFoo. Another issue is the object dependencies will not notify you that frmOne has been deprecated --- the current version is frmTwo.

Similarly, using Application.SaveAsText to create text files for database objects, then grepping the text files would not tell you frmOne has been deprecated.

You could try a different approach to identify which of the database objects are required. Create a new database file. Import the startup form from the old database. Open the new database, and the form to identify the missing items it needs. Import those. Lather, rinse, repeat.

If the application isn't driven from a startup form, ask the users which forms and reports they use, then import those.

This approach will be tedious, and could take a few hours. However, I doubt the other approaches would be dramatically faster. On the plus side, you're pretty much guaranteed that you won't be importing unneeded objects into the new database. And if you miss anything which is needed, you can import that from the saved copy of the old database.

Answer 2

This may be a case for using Track Name Autocorrect, with this turned on you can trace object dependencies.

It is by no means impossible to check code and events for form references with VBA.

Answer 3

I inherited an application with 20+ mdb front-ends with some of them using queries in other files. In addition to the solution that Remou mentioned, I also use a variation of this script to export all forms, queries, and reports to text files then grep through them to check if the object is used.

It's not perfect, but it also allows me to check for dependencies between mdbs - I'm not sure if you can do this with the built-in tools. I may have been suffering from NIH when I coded it up.

You may also want to look at mz-tools which has some tools to find unused code.

Answer 4

There is this small add-in, free and usefull vtools, that, among other possibilities, specifically allows you to search for values or references in all access objects (tables, queries, code, forms, ...).

Answer 5

Its easy.

Go into the VBA code editor (find View Code somewhere) CTRL + F to pull up the find options and click Search Project

so if you go through the form and report names you'll be able to find every single time they've been referenced programatically. This will not work to find if queries are used, for that you need the Database Documenter.