Monitor object activity

[jojo1]

Hello:
I need to cleanup a database which contains tons of tables and queries
and I would like to remove the unused/useless objects. Is there a way
to automate this? Like indexing the objects to monitor the activity? A
procedure? An utility tool maybe? Date Created and Modified is not
precise enough.
Thanks!

[Rick Wannall]

Re: Monitor object activity

If you're not in a rush for the data, you can do a pretty nice job of this
with some coding, though it will affect performance slightly.

Make yourself a table MyObjectLog, with columns for object type, object
name, date of last use, and number of uses. You could prepopulate that with
all types and names by using an append query that select records from
msysobjects. For readability, either change the Type column to text and
replace "5" with "query", etc, or set up constants and refer to them when
passing type info. (const OBJTYPE_QUERY as long = 5, etc). Make sure the
log table is in a backend, so that the data doesn't get overwritten with new
release of the app. If you are not using a split Front End/Back End
arrangement, make a new MDB and put the log table in it, then link to it
from your app MDB.

Write a function the accepts parameters for the type and name and creates a
recordset to retrieve the existing row for the object with the type and name
received by the function. Update the date and count columns.

Here's the not very pretty but I believe unavoidable messy part: You have
to add a call to the function in the Open event of each form or report, and
anywhere incode that you open a query (as with docmd.openquery ) or table
explicitly.

I don't know of any other method for gathering accurate information about
object usage. If there is one, I'd love to know it myself.

I say "not in a rush for the data" because now you just have to let the app
run for "long enough" (whatever that is) to get enough info to base your
decisions on.

There are other ways, depending on who will be running the app and how
tolerant they would be of hitting errors and waiting for recovery from the
error: If you are in an MDB, you just rename things, adding "_X" (or
anything you like) to the end of object names. As the app runs, if
something still in use has been renamed, you wil get the obvious error.
Recovery consists of getting into the MDB at the database window and fixing
the name of the object. One down, some unknown number to go.

This is a nerve-wracking approach if it's end-users that will see these
errors, so the log table approach is recommended unless it is basically you
and a hardy soul or two using the app during the research period.

HTH

[Arno R]

Re: Monitor object activity


"Rick Wannall" <cwannall@yahoo .com> schreef in bericht news:bGGcg.8026 0$H71.35246@new ssvr13.news.pro digy.com...[color=blue]
> If you're not in a rush for the data, you can do a pretty nice job of this
> with some coding, though it will affect performance slightly.
>
> Make yourself a table MyObjectLog, with columns for object type, object
> name, date of last use, and number of uses. You could prepopulate that with
> all types and names by using an append query that select records from
> msysobjects. For readability, either change the Type column to text and
> replace "5" with "query", etc, or set up constants and refer to them when
> passing type info. (const OBJTYPE_QUERY as long = 5, etc). Make sure the
> log table is in a backend, so that the data doesn't get overwritten with new
> release of the app. If you are not using a split Front End/Back End
> arrangement, make a new MDB and put the log table in it, then link to it
> from your app MDB.
>
> Write a function the accepts parameters for the type and name and creates a
> recordset to retrieve the existing row for the object with the type and name
> received by the function. Update the date and count columns.
>
> Here's the not very pretty but I believe unavoidable messy part: You have
> to add a call to the function in the Open event of each form or report, and
> anywhere incode that you open a query (as with docmd.openquery ) or table
> explicitly.
>
> I don't know of any other method for gathering accurate information about
> object usage. If there is one, I'd love to know it myself.
>
> I say "not in a rush for the data" because now you just have to let the app
> run for "long enough" (whatever that is) to get enough info to base your
> decisions on.
>
> There are other ways, depending on who will be running the app and how
> tolerant they would be of hitting errors and waiting for recovery from the
> error: If you are in an MDB, you just rename things, adding "_X" (or
> anything you like) to the end of object names. As the app runs, if
> something still in use has been renamed, you wil get the obvious error.
> Recovery consists of getting into the MDB at the database window and fixing
> the name of the object. One down, some unknown number to go.
>
> This is a nerve-wracking approach if it's end-users that will see these
> errors, so the log table approach is recommended unless it is basically you
> and a hardy soul or two using the app during the research period.
>
> HTH [/color]

Hmmmm, I think I would not like this at all...

Much, much better IMO you could use a tool like Rick Fisher's Find & Replace (or Speed Ferret) for this job.
I don't know Speed Ferret myself (I heard it's good), but I use F&R since Access 2.0
It is a very good tool. For this job you would need the cross-reference report (available in the registered version)

http://www.rickworld.com/products.html

Arno R

[jojo1]

Re: Monitor object activity

Thanks Rick.
This is a standalone mdb. No forms or reports. Some tables and queries
are used thru ColdFusion to display web live info. This mdb is used by
different users (which can query, view or update some tables thru the
web). Other tables and queries are used for performing data search
inside the mdb. Changing object names is not a problem, they cannot.
I still can read the sql statements in ColdFusion to get some of the
object names, it is a bit fastidious but well, it is possible, but it
resolves only one part of my task. Inside the mdb I can't see a way to
get a solution. Do you envision another way without using forms and
reports?

[jojo1]

Re: Monitor object activity

Interesting thanks. I check out the Help of F&R about the Xref report
but I am not sure if I can get something like a last used date for eahc
object. It seems to reference objects between them, like what tables
are used in what queries, did you already get results for my request?

[Arno R]

Re: Monitor object activity

F&R won't give you a last used date, no
Apparantly that is what you need. I misunderstood you.

Arno R

"jojo1" <pjolivalt@msn. com> schreef in bericht news:1148411011 .331790.51120@j 55g2000cwa.goog legroups.com...[color=blue]
> Interesting thanks. I check out the Help of F&R about the Xref report
> but I am not sure if I can get something like a last used date for eahc
> object. It seems to reference objects between them, like what tables
> are used in what queries, did you already get results for my request?
>[/color]

[Rick Wannall]

Re: Monitor object activity

No.

[Tom van Stiphout]

Re: Monitor object activity

On 23 May 2006 09:00:54 -0700, "jojo1" <pjolivalt@msn. com> wrote:

Long shot: there is a DLL that can be used to get the same information
as LdbViewer application. The name escapes me, but I'm sure you can
google for it. Perhaps it can give you what you need.
Otherwise: inspect your source code. Convert everything to text files,
and search for any of the tablenames and querynames using something as
simple as fc.exe.

-Tom.


[color=blue]
>Hello:
>I need to cleanup a database which contains tons of tables and queries
>and I would like to remove the unused/useless objects. Is there a way
>to automate this? Like indexing the objects to monitor the activity? A
>procedure? An utility tool maybe? Date Created and Modified is not
>precise enough.
>Thanks![/color]