我有一个数据库,其中有数百个表、宏和表单。
我的问题是,我需要找出与特定表相关的所有查询和宏。
我正在使用Microsoft Access 2000。
但是,即使我尝试了Access 2007中的对象依存关系,它也显示了大量错误并自动关闭。
是否有更简单的方法可以完成这项任务?
谢谢, Shanmugam
我有一个数据库,其中有数百个表、宏和表单。
我的问题是,我需要找出与特定表相关的所有查询和宏。
我正在使用Microsoft Access 2000。
但是,即使我尝试了Access 2007中的对象依存关系,它也显示了大量错误并自动关闭。
是否有更简单的方法可以完成这项任务?
谢谢, Shanmugam
您可以尝试直接对系统表执行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] & "*"))
您可能需要检查是否有访问系统表的权限...
希望这可以帮到您
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
On Error Goto 0
行应该是 On Error Goto Err_SearchDB
。这是否澄清了混淆? - mwolfe02对于像我一样找到这个页面的其他人,以下是一个变体,包括所有查询表或表达式中字符串出现的情况。(在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 "
;
SELECT DISTINCT
MSysObjects.Name, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Expression
FROM
MSysQueries
INNER JOIN
MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id;