Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MS Access: How to find all usages of a VBA function?

Tags:

vba

ms-access

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.

like image 724
Tom Avatar asked Oct 05 '22 04:10

Tom


1 Answers

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()"
like image 182
Heinzi Avatar answered Oct 10 '22 02:10

Heinzi