logo标签列表

如何在MS Access中查找与表相关的所有查询

ms-accessms-access-2007
16

我有一个数据库，其中有数百个表、宏和表单。

我的问题是，我需要找出与特定表相关的所有查询和宏。

我正在使用Microsoft Access 2000。

但是，即使我尝试了Access 2007中的对象依存关系，它也显示了大量错误并自动关闭。

是否有更简单的方法可以完成这项任务？

谢谢， Shanmugam

4个回答
30

您可以尝试直接对系统表执行SQL查询，以获取在2003+版本中以更用户友好的方式显示的依赖项。我不确定在2000年是否有效（它在2003+中有效），但值得一试：

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

您可能需要检查是否有访问系统表的权限...

希望这可以帮到您

1
嗨，Igor Turman，非常有帮助。您能否说明如何查找与特定查询相关的查询和表格？ - shanmugamgsn
谢谢。您在 MS Access 中从哪里执行此查询？ - 123
10
可以购买第三方软件来完成此操作，但我从未感到有必要。相反，我编写了一些过程来完成此操作。它们需要一个DAO的引用。
第一个过程（SearchQueries）仅搜索查询文本并且运行非常快。第二个过程（SearchDB）搜索表单、宏、查询、报告和代码。它需要较长时间，但非常彻底。使用方法应该很容易理解，如果有任何疑问，请提出问题。
以下是完整的过程文本：
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
感谢您的回复，mwolfe02。（抱歉回复晚了）。正如您所说，第一个模块很简单易懂。而第二个模块对我来说太复杂了。但我还没有尝试过两个模块。我明天会去办公室尝试两个模块，并让您知道结果。如果我还有不理解的地方，我会告诉您的。 - shanmugamgsn
2
非常感谢。请记住，为了使代码运行，您需要引用DAO（工具|引用|添加Microsoft Office数据访问引擎对象...）。 - ankostis
@mwolfe02 这可能是一个愚蠢的问题，但是当程序出错时为什么您有两个Err_Search DB术语，而程序应该转到Err_SearchDB？ - 114
@114：我不确定我理解你的问题。但是我检查了我的代码并意识到我的 On Error Goto 0 行应该是 On Error Goto Err_SearchDB。这是否澄清了混淆？ - mwolfe02
3

对于像我一样找到这个页面的其他人，以下是一个变体，包括所有查询表或表达式中字符串出现的情况。（在Access 2003和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://dev59.com/wWsz5IYBdhLWcg3wiIWe "
;
在此链接中，作者提供了其他变体，用于汇总和搜索所选对象：https://www.tek-tips.com/viewthread.cfm?qid=1417365 - Doug_Ivison
1
SELECT DISTINCT 
MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Expression
FROM 
MSysQueries 
INNER JOIN 
MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id;

这让我得到了一个我正在寻找的所有内容的表格。谢谢Igor。
1
你能否编辑一下，解释一下你对Igor的回答所做的更改吗？ - Nathan Tuggy
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接