Log Access User Activity?

[Me]

Hi, I have an interesting need:

after merging dozens of databases on a large company IT environment,
we would like to know, over time, which objects of the 3 MDBs left are
most often used by users, we suspect 80% of them aren't used.
Some users use forms, but some click directly on macros or
tables/queries.
is it possible to implement an activity logging mechanism in access,
which would record in a table an entry for every time the user opens
up an access object? the forms/reports/macros are easy, just add a
small update query at their beginning to log when they are open. But
what about queries/tables?

also, there are hundreds of objects! to go through each and every one
of them
and add the logging command, would be a task well circumvented... .
is there some API or property of the Access application (we use ver.
2000) that
can help with this?
I thought of having the SysCmd(acSysCmd GetObjectState, objtype,
objname)
command run from a startup form in the background, on a timer event,
in an endless loop on all objects in the mdb every couple of seconds
and update the log table if their status is active, but that seems...
a little too much overhead, as well as not 100% certain to catch all
activations.


any ideas?

thanks

hh.

[Larry Linson]

Re: Log Access User Activity?

As there are no events associated with viewing tables or queries, or
executing macros, the answer is "no, you can't do what you want". There is
no builtin logging feature.

In a developed application, it is possible to record use of Forms and
Reports, and that is all that you normally would allow, and the code to do
that can be added programmaticall y. But, it clearly will not satisfy your
requirements.

Larry Linson
Microsoft Access MVP


"Me" <heruti@lycos.c om> wrote in message
news:2d4c3262.0 410010854.1f7c0 c70@posting.goo gle.com...[color=blue]
> Hi, I have an interesting need:
>
> after merging dozens of databases on a large company IT environment,
> we would like to know, over time, which objects of the 3 MDBs left are
> most often used by users, we suspect 80% of them aren't used.
> Some users use forms, but some click directly on macros or
> tables/queries.
> is it possible to implement an activity logging mechanism in access,
> which would record in a table an entry for every time the user opens
> up an access object? the forms/reports/macros are easy, just add a
> small update query at their beginning to log when they are open. But
> what about queries/tables?
>
> also, there are hundreds of objects! to go through each and every one
> of them
> and add the logging command, would be a task well circumvented... .
> is there some API or property of the Access application (we use ver.
> 2000) that
> can help with this?
> I thought of having the SysCmd(acSysCmd GetObjectState, objtype,
> objname)
> command run from a startup form in the background, on a timer event,
> in an endless loop on all objects in the mdb every couple of seconds
> and update the log table if their status is active, but that seems...
> a little too much overhead, as well as not 100% certain to catch all
> activations.
>
>
> any ideas?
>
> thanks
>
> hh.[/color]

[Pieter Linden]

Re: Log Access User Activity?

heruti@lycos.co m (Me) wrote in message news:<2d4c3262. 0410010854.1f7c 0c70@posting.go ogle.com>...[color=blue]
> Hi, I have an interesting need:
>
> after merging dozens of databases on a large company IT environment,
> we would like to know, over time, which objects of the 3 MDBs left are
> most often used by users, we suspect 80% of them aren't used.
> Some users use forms, but some click directly on macros or
> tables/queries.
> is it possible to implement an activity logging mechanism in access,
> which would record in a table an entry for every time the user opens
> up an access object? the forms/reports/macros are easy, just add a
> small update query at their beginning to log when they are open. But
> what about queries/tables?
>
> also, there are hundreds of objects! to go through each and every one
> of them
> and add the logging command, would be a task well circumvented... .
> is there some API or property of the Access application (we use ver.
> 2000) that
> can help with this?
> I thought of having the SysCmd(acSysCmd GetObjectState, objtype,
> objname)
> command run from a startup form in the background, on a timer event,
> in an endless loop on all objects in the mdb every couple of seconds
> and update the log table if their status is active, but that seems...
> a little too much overhead, as well as not 100% certain to catch all
> activations.[/color]

In addition to what Larry said, you could hide the database window and
use the form from the Developer's Handbook and then since you'd be
driving everything from a form, you could grab the code from Allen
Browne's website and use that to do the logging. Won't be super
simple because you have so many forms etc, but that's about the only
option.