How to find unused object/items?

[amorosik]

I would like to build a procedure to search for reports present in my program but which are never started by any piece of code
So the first phase will be to make a list of the available reports, but then how to go through the code both present in the modules and present in the forms to verify that the name of the report never appears?
I know there are specialized programs that do that, but I'd like to understand exactly how they work, and therefore how to step through the code of modules and forms Do you know if there are examples of vba code that allow me to understand exactly how these programs work?

 

[ebs17]

With the Application.SaveAsText method, you can export the complete definitions of all access objects (forms, reports, macros, modules) in text files.
Text files with a plaint text are then easy to search.

 

[MajP]

Programming In The VBA Editor

www.cpearson.com

The code is here to loop all modules and procedures and search for text. Then simply write to a table the report and where it is called.
I did experience the issue with proc_kind which is resolved here

Automagic testing framework for VBA

Building on @RubberDuck's recommendations, I now have something I find... beautiful. I'm sure there's a couple of things left to polish - this site is about making great code out of good any code, ...

codereview.stackexchange.com

 

[jdraw]

Has anyone done this "Then simply write to a table the report and where it is called."? Not necessarily Report, but whatever and wherever?

 

[MajP]

Yes

 

[jdraw]

Thanks MajP. I suppose I should have asked that question a little differently.
Does anyone, who has done this, have code they can share and post in the forum?

This is the sort of thing Google has identified.

 

[MajP]

I have a whole bunch of code and a helper class that demos things you can do in video. I r o not do that specific case, but have most of the pieces.
I will try to demo this specific case.

 

[jdraw]

Thanks.

 

[Josef P.]

The challenge in this task is to detect if the report name is in an active code block.

Example:
Report name to be checked: "repXyzTotals"

Code location with the name:

private sub OpenSummaryReport()
     ' Const RepName as String = "repXyzTotals"
     ' replaced with:
     Const RepName as String = "repXyzSummary"
     '...
     DoCmd.OpenReport  RepName, ...
end sub

Note: the example is of course bad code style, but something like that could maybe happen.

 

[MajP]

You have to read line by line so you can check if it is commented out. You can get a 95% solution I am sure.

 

[Pat Hartman]

Have you looked at Total Access Analyzer by FMS? They have some pretty good tools. www.fmsinc.com Your employer will almost certainly pay for it given the amount of time it will save you and therefore them

 

[Minty]

VTools deep search can find a string pretty much anywhere within a database.

V-Tools

 

[ebs17]

V-Tools have open source code, regarding...

but I'd like to understand exactly how they work

 

[The_Doc_Man]

Of course, there is this problem: If you have a module with multiple Subs or Functions in it and one particular Sub in that module uses all of the other subs and functions therein, you would find in a cross-referencer or any other search tool that all of the entry points in the module are active ... except one.

 

[amorosik]

Have you looked at Total Access Analyzer by FMS? They have some pretty good tools. www.fmsinc.com Your employer will almost certainly pay for it given the amount of time it will save you and therefore them

Yes, I know the fame of FmsInc tools well
But I would like something more limited in functionality but over which I can have full control of the actions to be performed

 

[amorosik]

Of course, there is this problem: If you have a module with multiple Subs or Functions in it and one particular Sub in that module uses all of the other subs and functions therein, you would find in a cross-referencer or any other search tool that all of the entry points in the module are active ... except one.

I was thinking of dedicating a db table to record all the objects and their type contained in the program
Once stored permanently, it should be easy to check if the name of a form/report appears only once and therefore is essentially orphaned, as it is never used in the code of the procedure analysed.
From the first tests, however, the times seem much greater than what you see on the standard performance analyzer of Access or similar tools

 

[MajP]

I was thinking of dedicating a db table to record all the objects and their type contained in the program

That can be done in a query of the systems table. No need for a table.

 

[Pat Hartman]

MSysObjects includes the names of Forms, Reports, Tables, Queries, Macros, and Modules and a couple of pieces of information about them but the date information regarding Modules is wrong since the date changes each time Access closes whether anything got updated or not. And the date information regarding Forms and Reports may also be wrong depending on whether or not you changed anything that Jet cares about. Beyond what is in MSysObjects, you need to use VBA to examine objects and not all properties are accessible even then. This is one place where the Access developers didn't consider the professionals who might use Access to create actual applications so there are rudimentary at best options available for obtaining information regarding object properties.

 

[KitaYama]

but the date information regarding Modules is wrong since the date changes each time Access closes whether anything got updated or not.

@Pat Hartman can you elaborate on this please?

The following image is from a database that I use regularly on a daily basis. All the objects started with mod are modules.
I don't know if the shown DateUpdate is correct or not, but I know I open and close it several time. Yet the date shown is not from the last time the database was closed. You also see modManu that shows 2023/03 which I think I remember I updated it several months ago.

thanks.

 

[jdraw]

I'd like to see the query(ies) or code that shows where a procedure is called - as mentioned in #4.
I understand getting procedures and type(sub/function) from Forms/Reports/Modules and record in a table. But getting info about where the procedure is called from (eg MZTools Method Callers) is unclear.