Tracking what buttons are used

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm in charge of a fairly large Access 97 database and one of my new tasks
slim it down and slowly work on upgrading to Access 2003.
One way I want to do this is to eliminate any forms/queries/reports that
just are not used anymore. What I want to do is set up something that will
track which forms/queries/reports are being used. This will give me a
starting point for eliminating what's not being used.

Is this even possible to do and if so how would I go about doing this?

There are about 700 forms, 650 reports and 1200 queries so I would want to
be able to do this gobally without having to go into every form and report
tell it to count everytime it's opened.

Thanks for any help that can be provided.
 
Brian,

One idea would be to use a dummy, hidden form with a timer procedure
(run every 30 secs? one minute? or what?) in VBA which loops through all
open forms/reports and stores their names in a table. If you only want
the names and don't care about frequency, all you need is a table with
two fields, ObjectType and ObjectName; making a composite PK of the two
will ensure that you only get one record for each. The code could look
something like:

Private Sub Form_Timer()
For Each obj In Forms
strSQL = "INSERT INTO tblUsedObjects (ObjectType, ObjectName)"
strSQL = strSQL & " SELECT 'Form', '" & obj.Name & "'"
CurrentDb.Execute strSQL
Next
For Each obj In Reports
strSQL = "INSERT INTO tblUsedObjects (ObjectType, ObjectName)"
strSQL = strSQL & " SELECT 'Report', '" & obj.Name & "'"
CurrentDb.Execute strSQL
Next
End Sub

I have left out the queries, because it's not safe to follow a similar
approach, in that queries may be used by forms or reports or lookups
without ever being opened by the user (actually, they shouldn't be
opened directly by the user), so you might end up deleting a whole load
of queries just to find out then that you actually needed them!

A more elaborate approach would be to pre-populate the table with all
the existing form and report names, add a third field (Long) and use an
update query instead to increase the value of the field by one each
time. This would give you an indication of how long, relatively, each
form or report stays open (but not the number of times it has been
opened)... a questionable indicator. More elaborate indicators
(recording each and every time a form / report is opened, for how long
and, possibly, by which user) are possible, but would require at least
more frequent checking (every second?) which might become a problem, or
even the addition of code behing each and every object.

HTH,
Nikos
 
Brian,

I forgot to make the most important point I was thinking of as I read
your post: a database that contains about 700 forms, 650 reports and
1200 queries sounds to me like a design with no use of parametrization,
e.g. a separate query/report for each state, instead of one for all,
passing the state as a parameter. Is this the case? If yes, I suggest
you start from there! It involves quite some work in redesigning, but
it's the sensible thing to do, and it will pay.

HTH,
Nikos
 
This works perfectly and is exacly what I was looking for. Thanks for the
help. As for the design, yes it has no parametrization, really no overall
design concept. It hasn't had someone with total control over it in a couple
of years and people just added what they needed with no real regard to size
and efficency. It's going to be a fun and challenging project. I'm sure I'm
going to be back to these boards alot trying to figure stuff out.
Thanks again
Brian
 
Back
Top