List where objects are referenced? (for documentation purpose)

[jrtwynam]

Hello.

I have an Access tool that contains a whole bunch of tables, queries,
forms, and VBA code. We've been working on documenting the whole
database, because at the moment I'm the only one that understands it
in depth since I created it. What I'd like to do is list everywhere in
the tool that each *thing* is referenced. E.g. if I have a table
called "Rates", I need to list every query, macro, module, function,
form, and report that somehow requires the Rates table. That includes
any references within VBA code, plus any references within stacked
queries (i.e. if Query1 uses the Rates table, and Query2 uses Query1,
then the Rates table is required for Query2 to work).

Is there an easy way to do this? I'm using Access 2007, and I tried
the built-in documenter, but it seems to output a whole bunch of stuff
that I don't need and not much that I do need.

Thanks,

John

 

[Douglas J. Steele]

Take a look at Total Access Analyzer
http://www.fmsinc.com/MicrosoftAccess/BestPractices.html

A bit pricey, but very complete.

 

[Jack Leach]

I'm not sure about any "easy" way to do this, but maybe there's a few
shortcuts.

In A2k3 there was an object dependany feature that's supposed to give
information such as what you are looking for, which should work for
everything except the vba modules. I'm not sure where this would be in 07
(maybe it's what you already found that wasn't much help).

I believe that MZ-Tools (www.mztools.com) has a function to return some of
this information as well. Also, if I remember correctly, FMS
(www.fmsinc.com) has some utilities (to purchase) that may be able to get
everything you're looking for.

For VBA, Chip Pearson (www.cpearson.com) has an explaination of how to
directly edit the text in the VBA editor. This is written for excel, but I
think can be fitted to access as well. The whole project is a bit on the
advanced side, but if you can output all of your modules to a file and then
search the subsequent text for instances of each object, this may help some.
But thereagain, maybe just a copy/paste and a find function can do the same
thing.

I think your best bet would be to take a look at FMS's website and see if
they have something. The offer a myraid of different tools for various
analyzation processes, and seem to be well developed, if a little on the
pricey side.

No curealls, but maybe somewhere to start.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[John]

I'm not sure about any "easy" way to do this, but maybe there's a few
shortcuts.

In A2k3 there was an object dependany feature that's supposed to give
information such as what you are looking for, which should work for
everything except the vba modules.  I'm not sure where this would be in07
(maybe it's what you already found that wasn't much help).

I believe that MZ-Tools (www.mztools.com) has a function to return some of
this information as well.  Also, if I remember correctly, FMS
(www.fmsinc.com) has some utilities (to purchase) that may be able to get
everything you're looking for.

For VBA, Chip Pearson (www.cpearson.com) has an explaination of how to
directly edit the text in the VBA editor.  This is written for excel, but I
think can be fitted to access as well.  The whole project is a bit on the
advanced side, but if you can output all of your modules to a file and then
search the subsequent text for instances of each object, this may help some.  
But thereagain, maybe just a copy/paste and a find function can do the same
thing.

I think your best bet would be to take a look at FMS's website and see if
they have something.  The offer a myraid of different tools for various
analyzation processes, and seem to be well developed, if a little on the
pricey side.

No curealls, but maybe somewhere to start.

hth
--
Jack Leachwww.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."  
-Thomas Edison (1847-1931)







- Show quoted text -

Hi,

Thanks for the replies. I did find the Total Access Analyzer, and it
looks like it will do a lot of what I want. The problem is that this
is for work, so I don't want to pay for it myself, and getting work to
pay for it will take a while because of various IT policies (seems a
bit dumb for something that's relatively cheap for a larger
corporation, but of course there are procedures that we'd have to
follow).

I also found out how to access the code from the modules with VBA, but
I was hoping there would be an easier solution than parsing the code
to find out the function names and then searching all the code for
each function to find out where it's used.