which queries use table X?

[OldTymer]

Is there a way to find out which queries refer to a given table?

I have to modify how tables X, Y and Z are used throughout this app. I can
use FIND in the VB Editor to search all Forms and Modules in the whole
project for a table name -- but what about searching in queries? Or reports?

Bonus question: is there an easy way to find the 'dead wood' in a project?
I man things like unused queries, reports, tables, forms, etc. I can see
that quite a few people have carelessly hacked on this project over the
years, which makes it harder to "just fix" what I'm working on.

Thanks muchly.

 

[Rick B]

This is asked and answered often. Do a search. Try "unused".


Rick B

 

[OldTymer]

Rick B> This is asked and answered often. Do a search. Try "unused".

By "search" I assume you mean "Edit" menu -> "Find..." in the Visual Basic
Editor (VBE). Yes, I tried that and it does not search in the queries of the
project.

I want to local ALL uses of a given table throughout the project.

To clarify, it seems that VBE can locate SOME textual occurances of the
table name in VB code. That is fine and wonderful. But...

In Microsoft Access (MA) I have many, many objects of various types:
Queries, Reports, Forms, Tables, Modules, etc. MA does NOT seem to have a
"Find" function, but MA lists all the objects of the project.

VBE *does* have a "Find" function, but does not list all the objects of the
project (such as Queries).

So again, how can I find out what queries refer to a given table? Heck, if
I could export the whole project as text somehow and use NOTEPAD I would do
that.

Thanks.

 

[OldTymer]

This is asked and answered often. Do a search. Try "unused".

Did you mean "Do a search of the access forums"? If so, that was unclear
and, ultimately, unhelpful. I found only two related items...

One suggested a $299 product from FMV (www.fmsinc.com), which I had already
found myself. The demo version doesn't run on anything but its sample
database. And my employer won't spend that for a "one-shot" tool. He just
wants me to "fix his current problem".

The other referred to using "General" to identify unused VB code for a given
form. Besides not seeming to work, it's not relevant to my issue (see
Subject line).

Perhaps this isn't asked as often as it use to be?

 

[Rick B]

When I have deon this in the past, I applied code to my reports and forms
to log a record when they were opened. I only did this for the ones that I
did not think were used often. I left this in place for about a year to
catch any annual objects. I then renamed any objects that did not show
usage ( I put "X" in the first character of the name for Queries, Reports,
and Forms.) If users got errors indicating the obk\ject was missing, I
changed the name back.

Not a perfect solution, but it worked.

I coulda sworn I had seen posts for other suggestions out there. I was not
able to find much when I searched either. It seems that there were a few
posts mentioning tools (or software) that you could use. I never checked
them out, so they could have all been that $300 product you mentioned.

Hope that helps. MAybe others will respond with some other ideas.

Rick B

 

[John Vinson]

Is there a way to find out which queries refer to a given table?

I have to modify how tables X, Y and Z are used throughout this app. I can
use FIND in the VB Editor to search all Forms and Modules in the whole
project for a table name -- but what about searching in queries? Or reports?

Bonus question: is there an easy way to find the 'dead wood' in a project?
I man things like unused queries, reports, tables, forms, etc. I can see
that quite a few people have carelessly hacked on this project over the
years, which makes it harder to "just fix" what I'm working on.

Thanks muchly.

There are several tools that have been written by third parties to do
this. In increasing order of power (and price) consider:

Freeware: http://www3.bc.sympatico.ca/starthere/findandreplace
Find and Replace: http://www.rickworld.com
Speed Ferret: http://www.moshannon.com
Total Access Analyzer: http://www.fmsinc.com

John W. Vinson[MVP]

 

[Ed Robichaud]

FMS Access Analyzer will report out all object references and
interdependence (and many other things). I think the retail price is $199
www.fmsinc.com

-Ed

 

[Ken Snell [MVP]]

Put this subroutine in a regular module, and then run it from the Immediate
Window (provide the table name as a text string as the argument). It'll
return a list of all queries that use that table.

Public Sub FindQueriesThatUseAQueryOrTable(strFindString As String)
Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim intC As Integer
Set dbs = CurrentDb
For Each qdf In dbs.QueryDefs
If InStr(qdf.SQL, strFindString) > 0 Then Debug.Print qdf.Name
Next qdf
dbs.Close
End Sub

 

[Dan Artuso]

Hi,
This might get you started:

Public Sub SearchQueries(strKeyWords() As String)
Dim qryDef As DAO.QueryDef
Dim i As Integer

For Each qryDef In CurrentDb.QueryDefs
For i = 0 To UBound(strKeyWords)
If InStr(1, qryDef.SQL, strKeyWords(i), vbTextCompare) And InStr(1, qryDef.Name, "~", vbTextCompare) = 0 Then
Debug.Print qryDef.Name & " " & qryDef.SQL
End If
Next
Next


End Sub
Public Sub TestSearch()

Dim strArr(2) As String

strArr(0) = "tblMachine"
strArr(1) = "dfgdfg"
strArr(2) = "kjh"

SearchQueries strArr

End Sub

 

[gls858]

Is there a way to find out which queries refer to a given table?

I have to modify how tables X, Y and Z are used throughout this app. I can
use FIND in the VB Editor to search all Forms and Modules in the whole
project for a table name -- but what about searching in queries? Or reports?

Bonus question: is there an easy way to find the 'dead wood' in a project?
I man things like unused queries, reports, tables, forms, etc. I can see
that quite a few people have carelessly hacked on this project over the
years, which makes it harder to "just fix" what I'm working on.

Thanks muchly.

You don't mention what version of Access you are using but
in 2003 You ca n go to View > Object Dependencies and a
window will open showing your tables, queries and reports.
Not exactly what your wnating but it will show you what
queries are being used. Seach help for, object dependencies.

gls858

 

[George Nicholson]

Or:
You could use the Documenter to create a report that included the SQL code
for all your queries/reports, export the Documenter report to Word and then
do a "Find" on the desired table names within Word.

 

[OldTymer]

Or: You could use the Documenter to create a report that included the SQL code
for all your queries/reports, export the Documenter report to Word and then
do a "Find" on the desired table names within Word.

I liked this idea because I could spend the time once and search multiple
times more quickly.

Unfortunately it hung Access on a query called "QTest_Crosstab" -- high CPU
usage on MSACCESS.EXE for 10+ minutes on this 2.4GHz P4 with WinXP.

 

[OldTymer]

Unfortunately it hung Access...

And upon using the MS automated problem reporting, it notified me that this
machine needs Office SP3. Only a 48 minute download. Sigh.

 

[OldTymer]

Put this subroutine in a regular module, and then run it from the Immediate
Window (provide the table name as a text string as the argument). It'll
return a list of all queries that use that table.

:

Very slick! Worked like a charm. Thank you, Ken!

 

[OldTymer]

Thank Dan! This worked as well as Ken's previous post, in fact this version
creates identical output with the option of uncommenting the SQL:

Public Sub SearchInAllQueries(strKeyWord As String)
Dim qryDef As DAO.QueryDef
For Each qryDef In CurrentDb.QueryDefs
If InStr(1, qryDef.SQL, strKeyWord, vbTextCompare) And InStr(1,
qryDef.name, "~", vbTextCompare) = 0 Then
Debug.Print qryDef.name
' Debug.Print qryDef.SQL
End If
Next
End Sub

 

[OldTymer]

You don't mention what version of Access you are using but
in 2003 You ca n go to View > Object Dependencies and a
window will open showing your tables, queries and reports.

2002. :-(

Thanks, tho.

 

[OldTymer]

Freeware: http://www3.bc.sympatico.ca/starthere/findandreplace

Slow, but it seemed to do the trick! Plus free and easy to install and use.

Find and Replace: http://www.rickworld.com

30 day full eval, then $37

Speed Ferret: http://www.moshannon.com

$199 (very cute logo and site), semifunctional until registered

Total Access Analyzer: http://www.fmsinc.com

$299, demo version views only their demo project

 

[John Vinson]

Slow, but it seemed to do the trick! Plus free and easy to install and use.

30 day full eval, then $37

$199 (very cute logo and site), semifunctional until registered

$299, demo version views only their demo project

I use (and like) both Speed Ferret and Total Access Analyzer. For
heavy-duty development they're both worth the investment IMHO - and
though they overlap some in functionality, they're enough different
that I do use both of them.

John W. Vinson[MVP]