Finding all queries which use a table

[ChuckW]

Hi,

Does anyone know of a tool that can scan all queries in a database and find
if a certain table is used? I have a table called tblCustomerRollup which is
old and outdated. I want to see which of the 500 queries in my database use
this table without opeing every single one of them?

Thanks,

 

[Jeff Boyce]

Chuck

Sounds like a variation on Search/Replace. Try searching online for
"Database Documenter" as a starting point.

A couple of the commercial tools I've used include FMS, Inc.'s Total Access
Analyzer and Black Moshannon's Speed Ferret. There are a lot of free tools,
too.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Albert D. Kallal]

If you are using access 2003, then just right click on the table, and select
object dependences.

It will show you all forms, reports, and queries that are based on the
table.....

this option often forces to you enable track-name auto correct. I would
suggest turning that feature off once you are done viewing dependences....

 

[JP]

Here is a routine I developed to search for strings in queries.
Copy and paste this code into a VBA module.
For variable strSearch1, enter the string to serach for.
Then run the routine and the results will be in the Immediate window.


Public Sub Find_String_In_Queries()
'
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim strSearch1 As String
Dim strResults As String
Dim numFound As Long

strSearch1 = "..." 'enter table name or field name or any string to
search for

Set db = CurrentDb

strResults = ""
numFound = 0
For Each qdf In db.QueryDefs
strSQL = qdf.SQL

If InStr(1, strSQL, strSearch1) > 0 Then
numFound = numFound + 1
strResults = strResults & vbNewLine & " " & numFound & ") "
& qdf.Name
End If
Next

End Sub

 

[JP]

Sorry, I missed a line while I copied and pasted.

Add: Debug.Print strResults

below the Next and above End Sub

 

[Glint]

Hi JP,
Thanks for your response. I have a database (Access 2002) littered with
queries that I want to get to get rid off, but I find that difficult because
I sometimes cannot tell if the query is needed for a form or another query.
Can you help me?

 

[Jeff Boyce]

One approach is to rename a query (I prefix mine with "zzz" - this makes
them sort to to bottom but still remain recognizable), then run your
application. If it doesn't break, you didn't need it!

Of course, this depends on your ability to exercise EVERY aspect of your
application!

Good luck.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Glint]

Thanks Jeff.
As you rightly pointed out, it can be disaster if one overlooks any aspect
before deleting. If the application is large, another approach may be needed.