I've been charged with refactoring and extending the functionality of an big Access database. The existing VBA code is pretty bad, so I'd like to clean it up by removing uneccessary functions and simplifying the rest. I wonder how I can find all usages of the functions?
As for the VBA project itself, I can of course search for their names. As for the SQL queries: I've written a function which prints all queries to the VBE's intermediate window, so I can search them as well.
But many functions are also used in forms and reports. Is there a way to find them all? There are a lot of complex forms/reports, so simply having a look at one control after the another or removing the function and testing if everything still works is not feasible at all.
Unfortunately, there's no search function (that I know of) in Access which includes form and report properties.
However, there's an undocumented method SaveAsText
that copies forms and reports into text files. If you extract all your forms and reports...
Dim db As Database
Dim d As Document
Set db = CurrentDb()
For Each d In db.Containers("Forms").Documents
Application.SaveAsText acForm, d.Name, "C:\export\" & d.Name & ".frm"
Next
For Each d In db.Containers("Reports").Documents
Application.SaveAsText acReport, d.Name, "C:\export\" & d.Name & ".rpt"
Next
...you can use "regular" text search tools (such as findstr
, which is included in Windows) to find occurrences of certain words:
C:\export>findstr /I "\<myFunction\>" *
Form1.frm: OnClick ="=myFunction()"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With