DataBase - Creating a table with all the "object dependancies"

Asked By DawnTreader

03-Feb-10 12:11 PM

Hello All

here is some code that i found then modified. i consider it to be a poor
mans object dependancies tool with out turning on the crap that came with
2003.

Public Sub queryDocumentation()
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb
Dim tbl As TableDef

For Each tbl In db.TableDefs
'        DoCmd.RunSQL ("INSERT INTO mytblTablesInQueries (TableName) SELECT
'" & tbl.Name & "'")
For Each qdf In db.QueryDefs '.QueryDefs
If InStr(qdf.SQL, "" & tbl.Name & "") Then
DoCmd.RunSQL ("INSERT INTO mytblTablesInQueries (TableName,
QueryName) SELECT '" & tbl.Name & "', '" & qdf.Name & "'")
End If
Next qdf
Next tbl
End Sub

i am wondering if anyone can point me in the direction of what code i would
need to do the same for forms and reports. what i need to know is how to find
the "querydefs" for forms and reports. is there isomewhere that stores that
kind of information for a form and a report?

as always, any and all help appreciated

03-Feb-10 06:48 PM

This is far from trivial. I use Total Access Analyzer form
http://www.fmsinc.com for the purpose, and I find it well worth the money.

The problem is that there are so MANY places to look. The Form or Report has a
Recordsource, which might be a tablename - or might be a multitable query,
which may well be based on some *other* query, which may...

They'll also have Filter and OrderBy properties which may well reference
fields... but that reference may well be immaterial if the FilterOn and
OrderByOn properties are False.

A form is also very likely to have one or more Subforms, which have all the
same issues.

A Form (or even a Report) may have one or more Combo Boxes or Listboxes, with
their own Rowsources - which again are likely to be Queries, possibly
multitable queries.

That does not even touch the fact that a form or report very likely will
contain VBA code, which could contain ANYTHING.

I would not even venture to try to tackle this job! If you are really top-notch
at VBA and are intimately familiar with the Access object model... go for
it... but bear in mind that the good folks at FMS are both, and AFAIK they are
still testing the 2007 version.
--

John W. Vinson [MVP]

04-Feb-10 11:25 AM

Hello John

see the thing is though if i know what is needed by my forms and reports
then the rest is trickle down information. if i know the "first" or "top"
level then i can follow down through the dependancies.

what i am trying to accomplish is to clear my databases of non implemented
feature creep orphan items.

can you tell me what the objects are? or at least where to look?
--
As always, any and all help appreciated! :)

04-Feb-10 01:13 PM

The Form's Recordsource property, each Subform's Form object's Recordsource
property, and each combo and listbox's RowSource property.

it is a monstrous job. It can be done but it is almost easier to make a copy of
the database, delete a query, and test everything and see what breaks. Or...
buy Total Access Analyzer.
--

John W. Vinson [MVP]

04-Feb-10 09:39 PM

Well, you might try temporarily turning on Name AutoCorrect. It will
calculate and display most of the dependencies for you, but it is not
100%. It does not look at modules, macros or menus/toolbars, but it
certainly takes care of tables, queries, forms and reports. One
thing, though -- you have to have the tables in the same database as
the front-end objects. I am working on a big project completely
revamping naming conventions on an existing application and I just
took my front end, deleted the linked tables and imported the
tables.

The problem is that you cannot do this on a live back end, so you will
still have to do any work on the data tables a second time.

On the other hand, if you are not renaming fields or anything, you
do not have to worry about that.

If you *are*, you might find the quick-and-dirty utility I made for
this useful. it is the first link on my Access downloads page:

http://dfenton.com/DFA/download/Access/

--
David W. Fenton                  http://www.dfenton.com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/