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 somewhere that stores that
kind of information for a form and a report?

as always, any and all help appreciated

On Wed, 3 Feb 2010 09:11:01 -0800, DawnTreader
<(E-Mail Removed)> wrote:

>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 somewhere that stores that
>kind of information for a form and a report?

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 doesn't even touch the fact that a form or report very likely will
contain VBA code, which could contain ANYTHING.

I wouldn't even venture to try to tackle this job! If you're 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]

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!


"John W. Vinson" wrote:

> On Wed, 3 Feb 2010 09:11:01 -0800, DawnTreader
> <(E-Mail Removed)> wrote:
>
> >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 somewhere that stores that
> >kind of information for a form and a report?
>
> 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 doesn't even touch the fact that a form or report very likely will
> contain VBA code, which could contain ANYTHING.
>
> I wouldn't even venture to try to tackle this job! If you're 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]
> .
>

On Thu, 4 Feb 2010 08:25:01 -0800, DawnTreader
<(E-Mail Removed)> wrote:

>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?

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

It's a monstrous job. It can be done but it's 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]

=?Utf-8?B?RGF3blRyZWFkZXI=?= <(E-Mail Removed)>
wrote in news:F3C1C3D6-1BE2-4454-BAD1-(E-Mail Removed):

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

Well, you might try temporarily turning on Name AutoCorrect. It will
calculate and display most of the dependencies for you, but it's not
100%. It doesn't 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 can't do this on a live back end, so you'll
still have to do any work on the data tables a second time.

On the other hand, if you're not renaming fields or anything, you
don't have to worry about that.

If you *are*, you might find the quick-and-dirty utility I made for
this useful. It's 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/