How do you find ALL queries used in forms and reports?

[M Skabialka]

I have a database which has undergone many revisions, and would now like to
clean it up. Since all reports are run from button on forms, it was easy to
find out which reports were still being used by searching through the VB
code. I aslo cleaned out older unused forms. I then opened all of the
remaining forms/reports and subforms/reports to find their control source,
so know some of the queries are still used. I also use queries for combo
boxes on forms and charts and other drawings on reports, etc. It seems as
though I have far too many queries I am not sure of the use of.

Is there any way to write a VB module which will search though all of the
forms and reports to see whether I am using queries anywhere on them, e.g.
in charts and so on? I'd hate to delete one that isn't used often then find
out it was necessary! All of my queries start with "qry...."

Thanks,
Mich

 

[Sprinks]

The best tool that I know of is the Total Access Documenter add-in. See the
URL below:

http://www.fmsinc.com/Products/analyzer/WhyEndUsers.html

Hope that helps.
Sprinks

 

[M Skabialka]

The first time I tried to run this I got an error message (didn't write it
down). Closed the box and tried again. Now I always get an error saying

Documenter
Table 'doc_tblObjects' already exists

Can this only be done once? I tried it on only one form. Then a table and
a query. Same error each time.

Under Tools, options I checked the boxes to show system and hidden objects
but there is no 'doc_tblObjects' under tables, queries or anywhere.

What have I done! Have I broken something? Help!

Mich

 

[Sprinks]

I agree that Joseph that the Documenter will give you a lot you don't need,
but it may not give you what you're looking for.

It will tell you the RecordSource of every form and report, and will list
the code underlying each so that you can scan it (which you've already done).
It will also list the RowSource of any combo boxes. But it won't do any
cross-referencing between different objects; you'll have to search all of it
yourself.

Good luck.
Sprinks

 

[M Skabialka]

I was able to find out where the table is - it is located at
C:\Documents and Settings\<user>\Application
Data\Microsoft\Access\ACWZUSR.MDT
I re-named doc_tblObjects, the only table with a current date, and was able
to run the documenter again.

I ran it on the forms, exported to Word, and found half a dozen instances
that I had missed of queries used on the forms. Thanks goodness I always
name them "qry...", because the report was 580 pages long! I could also
have looked for RowSource or RecordSource but I sometimes just put the
Select statement there so this gives too much info.

My suggestion to Microsoft - When we run the documenter, give us a list of
all of the items which could be displayed in the report (the default being
maybe object name only) and let us select the rest e.g.
PrtDevNames: no PrtMip:
no

RecordLocks: no RecordSelectors:
no

RecordsetType: no RecordSource:
YES

RowHeight: no ScrollBars:
no

ShortcutMenu: no ShowGrid:
no

SubdatasheetExpande no SubdatasheetHeight: no



Then we could look only at items we need to see.

Thanks for the help,
Mich

 

[George Nicholson]

Access Analyzer www.fmsinc.com

but it ain't free

 

[aaron.kempf]

what you need is speedferret

it is absolutely wonderful

oh yeah-- and dont worry about Microsoft-- they wont actually FIX BUGS
IN THEIR PRODUCTS BECAUSE THEY ARE TOO BUSY COMING UP WITH THE NEXT BIG
THING

screw that company