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]

Re: Tool for Isolating Unused Reports, Queries, etc

Mike Thomas wrote:
[color=blue]
> 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
>
>[/color]
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.OpenRepor t, 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.openrepor t 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.OpenRepor t strReportName)
End Sub

This would be a real PITA.

[Don Leverton]

Re: Tool for Isolating Unused Reports, Queries, etc

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 "evolutiona ry" 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 <mike@ease.co m> wrote in message
news:tpvkc.1174 $eH1.667044@new ssvr28.news.pro digy.com...[color=blue]
> Access 2000 - Is there a 3rd party tool available, or perhaps some method
> built into Access 2000, which could isolate unused Access reports,[/color]
queries,[color=blue]
> forms, etc?
>
> I have a 5 year old app which has not always been cleaned up. I know[/color]
there[color=blue]
> are quite a few such no longer used items. I have a "Find" utility which[/color]
is[color=blue]
> good, but I need to look for every component individually - it is very[/color]
time[color=blue]
> consuming because there are hundreds of components in the app.
>
> Many thanks
> Mike Thomas
>
>[/color]

[Bradley]

Re: Tool for Isolating Unused Reports, Queries, etc

Salad wrote:[color=blue]
> Mike Thomas wrote:
>[color=green]
>> 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
>>
>>[/color]
> 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.OpenRepor t, 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.openrepor t 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.OpenRepor t strReportName)
> End Sub
>
> This would be a real PITA.[/color]


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]

Re: Tool for Isolating Unused Reports, Queries, etc

Bradley wrote:
[color=blue]
> Didn't know Speedferret would do this.. thought it only did global
> find/replace?[/color]

You're probably correct. I don't have it. If it's just for a global
search/replace I'll roll my own.

[color=blue]
> 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....[/color]

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