code used to analyze database objects - Documentor not enough

[Lisab]

I've inherited a large Access database with a lot of connected tables, tons
of queries, and loads of macros. I would like to be able to do the following:

1. Run code to count the number of database objects -- What is the syntax
to return the number of Macros, Reports, and Forms in the database?
a. db.TableDefs.Count – will return number of tables
b. db.QueryDefs.Count – will return number or queries

2. Is there a way to find out every query, report, form and/or macro a
given table is used in. For example given tblMyTable, is there code that
will return every query that uses tblMyTable ? also, given qryMyQuery, is
there code that will return every macro that uses qryMyQuery and every form
that uses qryMyQuery.

***Using Access 2000 or 2003***

 

[Klatuu]

Not built in to Access. You have to have an external utility. Here is the
one I use. It is inexpensive and works well for me.

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

You can download a limited free trial version, but the full version is only
$39.00 US.

You can google the web, there are others, but many are very expenisve. One
to stay away from is Speed Ferret. It only works up through 2000 and the
customer support is weak. They have been promising a 2003 version for over 3
years and it still is not out. But, now they are even more behind the curve.

 

[Lisab]

Is it the "Find and Replace" download?

Not built in to Access. You have to have an external utility. Here is the
one I use. It is inexpensive and works well for me.

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

You can download a limited free trial version, but the full version is only
$39.00 US.

You can google the web, there are others, but many are very expenisve. One
to stay away from is Speed Ferret. It only works up through 2000 and the
customer support is weak. They have been promising a 2003 version for over 3
years and it still is not out. But, now they are even more behind the curve.

 

[Klatuu]

Yes. Don't let the name confuse you.
Part of what you can do is enter an object name and it will produce a report
of every place in the application that object is referenced.

 

[Lisab]

Thanks

I figured out how to get a count of the forms and macros.

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject

dbs.AllForms.Count
dbs.AllMacros.count

 

[Klatuu]

Yes, but I would suggest more specific typing in the Dim statements:
Dim obj As AccessObject, dbs As CurrentProject

I don't know what you are using obj for, but if there is a specific type you
can use, it would be better.