How to find all queries related to table in MS Access

Question

I have a database. In this i have hundreds of tables,macros and forms. No my problem is i have to find what all queries,macros that are related to specific table.

I'm using microsoft acess 2000.

But i even i tried objet dependencies in access 2007, it showed plenty of errors and close automatically.

Is this there any easy way to get this???

Thanks, Shanmugam

Spruik

Answer 1

You can try to execute SQL Query against system tables directly to get dependencies that are shown in 2003+ versions in more user-friendly way. I am not sure if that works on 2000 (it does in 2003+) but it is worth trying:

SELECT DISTINCT MSysObjects.Name
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId=MSysObjects.Id
WHERE (((MSysQueries.Name1) Like "*" & [TableName] & "*")) OR (((MSysQueries.Name2) Like "*" & [TableName] & "*"))

You may need to check if you have permissions to access system tables...

Hope this helps

Mormon

Hi Igor Turman, it was really helpful. can you say how to find queries and tables related to a specific query? – Spruik

Thank you. Where do you execute this query from within MS Access? – Evenfall

Answer 2

You can buy third-party software that will do this for you, but I've never felt the need for that. Instead, I wrote a couple of procedures that will do this. They require a reference to DAO.

The first one (SearchQueries) searches the text of queries only and runs quite fast. The second (SearchDB) searches forms, macros, queries, reports, and code. It takes a bit longer but is very thorough. The usage should be pretty self-explanatory but ask questions if you're unsure of anything.

Here's the full text of the procedures:

Sub SearchQueries(SearchText As String, _
                  Optional ShowSQL As Boolean = False, _
                  Optional QryName As String = "*")
    On Error Resume Next
    Dim QDef As QueryDef

    For Each QDef In CurrentDb.QueryDefs
        If QDef.Name Like QryName Then
            If InStr(QDef.SQL, SearchText) > 0 Then
                Debug.Print QDef.Name
                If ShowSQL Then Debug.Print QDef.SQL & vbCrLf
            End If
        End If
    Next QDef
End Sub


'Updated: 1/19/09 Limit search by object name pattern
Sub SearchDB(SearchText As String, _
             Optional ObjType As AcObjectType = acDefault, _
             Optional ObjName As String = "*")
Dim db As Database, obj As AccessObject, Ctl As Control, Prop As Property
Dim Frm As Form, Rpt As Report, mdl As Module
Dim objLoaded As Boolean, Found As Boolean, Instances As Long
Dim SLine As Long, SCol As Long, ELine As Long, ECol As Long

    On Error GoTo Err_SearchDB

    Set db = CurrentDb
    Application.Echo False

    '===============================================
    'Search queries
    If ObjType = acDefault Or ObjType = acQuery Then
        Debug.Print "Queries:"
        SearchQueries SearchText, False, ObjName
        Debug.Print vbCrLf
    End If


    '===============================================
    'Search forms
    If ObjType = acDefault Or ObjType = acForm Then
        Debug.Print "Forms:"
        On Error Resume Next
        For Each obj In CurrentProject.AllForms
            If obj.Name Like ObjName Then
                objLoaded = obj.IsLoaded
                If Not obj.IsLoaded Then DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
                Set Frm = Application.Forms(obj.Name)
                For Each Prop In Frm.Properties
                    Err.Clear
                    If InStr(Prop.Value, SearchText) > 0 Then
                        If Err.Number = 0 Then
                            Debug.Print "Form: " & Frm.Name & _
                                        "  Property: " & Prop.Name & _
                                        "  Value: " & Prop.Value
                        End If
                    End If
                Next Prop
                If Frm.HasModule Then
                    SLine = 0: SCol = 0: ELine = 0: ECol = 0: Instances = 0
                    Found = Frm.Module.Find(SearchText, SLine, SCol, ELine, ECol)
                    Do Until Not Found
                        Instances = Instances + 1
                        SLine = ELine + 1: SCol = 0: ELine = 0: ECol = 0
                        Found = Frm.Module.Find(SearchText, SLine, SCol, ELine, ECol)
                    Loop
                    If Instances > 0 Then Debug.Print "Form: " & Frm.Name & _
                       "  Module: " & Instances & " instances"

                End If
                For Each Ctl In Frm.Controls
                    For Each Prop In Ctl.Properties
                        Err.Clear
                        If InStr(Prop.Value, SearchText) > 0 Then
                            If Err.Number = 0 Then
                                Debug.Print "Form: " & Frm.Name & _
                                            "  Control: " & Ctl.Name & _
                                            "  Property: " & Prop.Name & _
                                            "  Value: " & Prop.Value
                            End If
                        End If
                    Next Prop
                Next Ctl
                Set Frm = Nothing
                If Not objLoaded Then DoCmd.Close acForm, obj.Name, acSaveNo
                DoEvents
            End If
        Next obj
        On Error GoTo Err_SearchDB
        Debug.Print vbCrLf
    End If


    '===============================================
    'Search modules
    If ObjType = acDefault Or ObjType = acModule Then
        Debug.Print "Modules:"
        For Each obj In CurrentProject.AllModules
            If obj.Name Like ObjName Then
                objLoaded = obj.IsLoaded
                If Not objLoaded Then DoCmd.OpenModule obj.Name
                Set mdl = Application.Modules(obj.Name)
                SLine = 0: SCol = 0: ELine = 0: ECol = 0: Instances = 0
                Found = mdl.Find(SearchText, SLine, SCol, ELine, ECol)
                Do Until Not Found
                    Instances = Instances + 1
                    SLine = ELine + 1: SCol = 0: ELine = 0: ECol = 0
                    Found = mdl.Find(SearchText, SLine, SCol, ELine, ECol)
                Loop
                If Instances > 0 Then Debug.Print obj.Name & ": " & Instances & " instances"
                Set mdl = Nothing
                If Not objLoaded Then DoCmd.Close acModule, obj.Name
            End If
        Next obj
        Debug.Print vbCrLf
    End If


    '===============================================
    'Search macros
    If ObjType = acDefault Or ObjType = acMacro Then
        'Debug.Print "Macros:"
        'Debug.Print vbCrLf
    End If


    '===============================================
    'Search reports
    If ObjType = acDefault Or ObjType = acReport Then
        Debug.Print "Reports:"
        On Error Resume Next
        For Each obj In CurrentProject.AllReports
            If obj.Name Like ObjName Then
                objLoaded = obj.IsLoaded
                If Not obj.IsLoaded Then DoCmd.OpenReport obj.Name, acDesign
                Set Rpt = Application.Reports(obj.Name)
                For Each Prop In Rpt.Properties
                    Err.Clear
                    If InStr(Prop.Value, SearchText) > 0 Then
                        If Err.Number = 0 Then
                            Debug.Print "Report: " & Rpt.Name & _
                                        "  Property: " & Prop.Name & _
                                        "  Value: " & Prop.Value
                        End If
                    End If
                Next Prop
                If Rpt.HasModule Then
                    SLine = 0: SCol = 0: ELine = 0: ECol = 0: Instances = 0
                    Found = Rpt.Module.Find(SearchText, SLine, SCol, ELine, ECol)
                    Do Until Not Found
                        Instances = Instances + 1
                        SLine = ELine + 1: SCol = 0: ELine = 0: ECol = 0
                        Found = Rpt.Module.Find(SearchText, SLine, SCol, ELine, ECol)
                    Loop
                    If Instances > 0 Then Debug.Print "Report: " & Rpt.Name & _
                       "  Module: " & Instances & " instances"

                End If
                For Each Ctl In Rpt.Controls
                    For Each Prop In Ctl.Properties
                        If InStr(Prop.Value, SearchText) > 0 Then
                            Debug.Print "Report: " & Rpt.Name & _
                                        "  Control: " & Ctl.Name & _
                                        "  Property: " & Prop.Name & _
                                        "  Value: " & Prop.Value
                        End If
                    Next Prop
                Next Ctl
                Set Rpt = Nothing
                If Not objLoaded Then DoCmd.Close acReport, obj.Name, acSaveNo
                DoEvents
            End If
        Next obj
        On Error GoTo Err_SearchDB
        Debug.Print vbCrLf
    End If

Exit_SearchDB:
    Application.Echo True
    Exit Sub
Err_SearchDB:
    Application.Echo True
    Debug.Print Err.Description
    Debug.Assert False
    Resume
End Sub

Sullins

Thanks for your reply mwolfe02. (Sorry for late reply). As u said First module was simple and easy to understand. While second was too complex for me. But i didnt try both yet. I will get to office by tomo and try both and let you know. If i didnt understand further i will let know. – Spruik

Thank you very much. Just remember that for the code to run you need to reference DAO (Tools|References|Add Microsoft Office Data Acceess Engine Objects...) – Donor

@Sullins This might be a silly question, but why do you have two Err_Search DB terms when the program is supposed to go to Err_SearchDB on error? – Autochthon

@114: I'm not sure I understand your question. But I reviewed my code and realized my On Error Goto 0 lines should have been On Error Goto Err_SearchDB. Does that clear up the confusion? – Sullins

Answer 3

For others who find this page as I did, below is a variation that includes occurences of a string, in all queries' tables or expressions. (This worked in both Access 2003 and Access 2013.)

SELECT DISTINCT 
MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Expression
FROM 
MSysQueries 
INNER JOIN 
MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE 
(   (((MSysQueries.Name1) Like "*" & [String to search for] & "*")) 
 OR (((MSysQueries.Name2) Like "*" & [String to search for] & "*"))
 OR (((MSysQueries.Expression) Like "*" & [String to search for] & "*"))  )

And "Comment:  You will be prompted once, for the [String to search for]"<>""
And "Comment:  The starting point for this code came from link:"<>
"https://mcmap.net/q/715395/-how-to-find-all-queries-related-to-table-in-ms-access# "
;

Yonne

At this link, the author gives other variations, for summarizing and searching selected objects: tek-tips.com/viewthread.cfm?qid=1417365 – Yonne

Answer 4

SELECT DISTINCT 
MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Expression
FROM 
MSysQueries 
INNER JOIN 
MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id;

This gave me a table of everything I was looking for. Thanks Igor.

Interrogation

Can you edit to explain the changes you made to Igor's answer that led to this? – Multifaceted