Tool for Isolating Unused Reports, Queries, etc

[Mike Thomas]

Access 2000 - Is there a 3rd party tool available, or perhaps some method
built into Access 2000, which could isolate unused Access reports, queries,
forms, etc?

I have a 5 year old app which has not always been cleaned up. I know there
are quite a few such no longer used items. I have a "Find" utility which is
good, but I need to look for every component individually - it is very time
consuming because there are hundreds of components in the app.

Many thanks
Mike Thomas

[Salad]

Mike Thomas wrote:

Access 2000 - Is there a 3rd party tool available, or perhaps some method
built into Access 2000, which could isolate unused Access reports, queries,
forms, etc?

I have a 5 year old app which has not always been cleaned up. I know there
are quite a few such no longer used items. I have a "Find" utility which is
good, but I need to look for every component individually - it is very time
consuming because there are hundreds of components in the app.

Many thanks
Mike Thomas

You could check out SpeedFerret.

You could roll-your-own if you are a programmer. You would need the
following knowdedge. For/Next, the Document object in order to check
all form/report/modules for references to Docmd.OpenReport, the Modules
object, how to scan all controls in a report to see if any are subforms,
how to add a record to a table (you would want to store the name of all
existing reports and in another table all reports you find for
comparision purposes), the FindMethod for the Module object, and the
ability to go back in lines in case the report is opened via a variable
name instead of the name of the report.

In fact, that would be the most difficult part of the program. Here are
2 examples

Dim stDocName As STring
stDocName = "TestReport"
Docmd.openreport stDocName

You'd need to loop back a line to see what stDocName is
Or

Dim strReport As STring
strReport = "TestReport"
Call OpenReportFile strRerport

Sub OpenReportFile(strReportName As STring)
Docmd.OpenReport strReportName)
End Sub

This would be a real PITA.

[Don Leverton]

Hi Mike,

There is a hidden table (in Access97 anyway) called MSysObjects. That table
contains a couple of fields that may help you to determine how "current"
these objects are, perhaps?
The fields are DateCreate and DateUpdate.

In my way of thinking, the objects that have not been updated in some time
are likely objects that don't get used? Most forms / reports (at least in my
apps) seem to go thru an "evolutionary" process. <grin>

This SQL (query) will show those objects that haven't changed in the last
year...

SELECT MSysObjects.*
FROM MSysObjects
WHERE (((MSysObjects.DateUpdate)<Now()-365));

Some advice before proceeding with deletion of objects, however...
Make SURE that you have a reliable backup!

HTH,
Don

Mike Thomas <mi**@ease.com> wrote in message
news:tp*******************@newssvr28.news.prodigy. com...

Access 2000 - Is there a 3rd party tool available, or perhaps some method
built into Access 2000, which could isolate unused Access reports, queries, forms, etc?

I have a 5 year old app which has not always been cleaned up. I know there are quite a few such no longer used items. I have a "Find" utility which is good, but I need to look for every component individually - it is very time consuming because there are hundreds of components in the app.

Many thanks
Mike Thomas

[Bradley]

Salad wrote:

Mike Thomas wrote:

Access 2000 - Is there a 3rd party tool available, or perhaps some
method built into Access 2000, which could isolate unused Access
reports, queries, forms, etc?

I have a 5 year old app which has not always been cleaned up. I
know there are quite a few such no longer used items. I have a
"Find" utility which is good, but I need to look for every component
individually - it is very time consuming because there are hundreds
of components in the app.

Many thanks
Mike Thomas

You could check out SpeedFerret.

You could roll-your-own if you are a programmer. You would need the
following knowdedge. For/Next, the Document object in order to check
all form/report/modules for references to Docmd.OpenReport, the
Modules object, how to scan all controls in a report to see if any
are subforms, how to add a record to a table (you would want to store
the name of all existing reports and in another table all reports you
find for comparision purposes), the FindMethod for the Module object,
and the ability to go back in lines in case the report is opened via
a variable name instead of the name of the report.

In fact, that would be the most difficult part of the program. Here
are 2 examples

Dim stDocName As STring
stDocName = "TestReport"
Docmd.openreport stDocName

You'd need to loop back a line to see what stDocName is
Or

Dim strReport As STring
strReport = "TestReport"
Call OpenReportFile strRerport

Sub OpenReportFile(strReportName As STring)
Docmd.OpenReport strReportName)
End Sub

This would be a real PITA.

Didn't know Speedferret would do this.. thought it only did global
find/replace?

A better tool is TotalAccess Analyser (not particulary cheap though but
it does have a lot of good functions).

I'm tempted to write my own sometime though....
--
regards,

Bradley

[Salad]

Bradley wrote:

Didn't know Speedferret would do this.. thought it only did global
find/replace?
You're probably correct. I don't have it. If it's just for a global
search/replace I'll roll my own.

A better tool is TotalAccess Analyser (not particulary cheap though
butit does have a lot of good functions).

I'm tempted to write my own sometime though....

I wouldn't be surprised if most of us have the code to do what we need
in some module.