473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Tool for Isolating Unused Reports, Queries, etc

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
Nov 12 '05 #1
4 2278
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.
Nov 12 '05 #2
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

Nov 12 '05 #3
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
Nov 12 '05 #4
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.

Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
6
by: Scott Simpson | last post by:
What is a good tool for running XPath queries on Linux? I have the O'Reilly XPath book and I'm using XPath Visualizer on Windows and that seems to work fine, but I'm looking for something on Linux....
2
by: OM | last post by:
Is there a simple program that will give me a diagram of all the sources of reports (queries and tables, and sometimes forms I guess). Thanks Rob Lepper
4
by: Mike Thomas | last post by:
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...
0
by: Jim R | last post by:
Simple probably. Table has 2 rows 1) Company Name 2) No Months Delinquent. Want to write a simple report that gives by company name, Total loans, Total 0, 1, 2, 3 and over 4 months delinquent....
2
by: Max | last post by:
I am editting a database created in access 2003 by another programmer. This person has left the company for which the database was created and no documentation. So that I could work on this...
1
by: John K. Humkey | last post by:
I'm at a loss (and, like a hard-core alcoholic) the "experts" I'm working with won't even acknowledge there is a problem. . . Is there any way to run "interactive transactions" at one priority...
2
by: MyEmailList | last post by:
We have an Access data base with severl tables, queries and forms. We want to put it on the web. Is there a tool that will convert the Access queries and forms to ASP? Sorta like "splitting"...
11
by: Executable | last post by:
Hi, I have a question. I have a table of several records each record contains about 20 columns and of course several rows. I would like to generate a query which checks each cell (not each...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.