3

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.

Community
  • 1
  • 1
Taylor Price
  • 622
  • 1
  • 8
  • 21

5 Answers5

2

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • 1
    Is there an easy way to determine that rptFoo is needed by the new database file? It seems like you would need to run every report to find the ones that are missing. Of course, that's probably a valuable exercise on an inherited application. – chip Oct 17 '11 at 15:00
  • 1
    It probably depends on how the app is organized. If the users open rptFoo only from the Database Window (Access <= 2003) or Navigation Panel (Access >= 2007), there is no way to determine it's required by inspecting database properties or code. If all user operations are driven from something like a switchboard menu form, then you could inspect code/macros/switchboard table to figure out which db objects are required. However, I think your second point is more important. Before refactoring anything, he should check with the users first. Avoid refactoring parts which aren't needed ... – HansUp Oct 17 '11 at 17:00
  • anymore. Avoid cleaning up code to do an operation better only to find out that that operation must be changed for current needs. There's just no substitute for understanding the inherited application before making any changes to it. Ask the users. – HansUp Oct 17 '11 at 17:02
  • 1
    @HansUp The application is driven from a main menu. Your solution is likely what I will do. I was trying to avoid having to go through all of the forms for a variety of reasons, but as chip pointed out, it will make for an important learning experience. Your point about checking with the users is spot on. I do my best to make sure they've got what they need in the new features I'm developing. I hope that they will take / make the time to go over some of the other older items and show me what meets their needs. – Taylor Price Oct 17 '11 at 21:50
  • 1
    @chip You're right that forcing myself to go through all of the forms would be an incredibly valuable exercise to understand what is going on in the application. – Taylor Price Oct 17 '11 at 21:51
0

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.

serakfalcon
  • 3,501
  • 1
  • 22
  • 33
0

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thanks for the Track Name Autocorrect suggestion. You're right that checking in the VBA is easy. Looking for forms / reports attached to buttons is more what I'm concerned with. I'll take a look at Track Name Autocorrect. – Taylor Price Oct 14 '11 at 22:18
  • @Taylor I also meant `For each frm In Currentproject.AllForms ... For Each Ctl In Forms(frm.Name).Controls ...` and so on. Quite a short bit of VBA should do it. – Fionnuala Oct 14 '11 at 22:27
0

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.

Community
  • 1
  • 1
chip
  • 2,262
  • 2
  • 20
  • 24
0

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, ...).

Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • Thanks for the suggestion of vtools. I'll take a look at them. They sound potentially useful. I will probably go with the solution @HansUp suggested simply to learn what is going on in this particular application. – Taylor Price Oct 17 '11 at 22:08