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]

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]

"Rick Wannall" <cw******@yahoo.com> schreef in bericht news:bG*******************@newssvr13.news.prodigy. com...

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

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]

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]

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]

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

Arno R

"jojo1" <pj*******@msn.com> schreef in bericht news:11*********************@j55g2000cwa.googlegro ups.com...

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?

[Rick Wannall]

No.

[Tom van Stiphout]

On 23 May 2006 09:00:54 -0700, "jojo1" <pj*******@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.

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!