Create Table 'Application_Query_Where_Used_Analysis' with the following fields
txtQuery_Name (text)
fQuery_In_Use_By_DB (yes/no)
txtWhere_Used_Type (text)
txtWhere_Used_Description (text)
Create a form with the following code
Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim dbs As Database
Dim qdfGetQueryNames As QueryDef
Dim frmGetActiveForm As Form
Dim repGetActiveReport As Report
Dim ctrForms As Container
Dim ctrReports As Container
Dim ctrModules As Container
Dim mdlModules As Module
Dim mdlFormModules As Module
Dim mdlReportModules As Module
Dim docContainerName As document
Dim strCheckQueryIsUsedByDatabase As String
Dim strGetGetQueryName As String
Dim lngSLine As Long
Dim lngSCol As Long
Dim lngELine As Long
Dim lngECol As Long
Dim intRecordCounter As Integer
On Error Resume Next
Set dbs = CurrentDb
Set ctrForms = dbs.Containers!Forms
Set ctrReports = dbs.Containers!Reports
Set ctrModules = dbs.Containers!Modules
DoCmd.RunSQL ("DELETE [Application_Query_Where_Used_Analysis].* FROM Application_Query_Where_Used_Analysis;")
'Check Form Recordsource & Procedures
For Each docContainerName In ctrForms.Documents
dbs.QueryDefs.Refresh
DoCmd.OpenForm docContainerName.Name, acDesign
Set frmGetActiveForm = Forms(docContainerName.Name)
Set mdlFormModules = frmGetActiveForm.Module
For Each qdfGetQueryNames In dbs.QueryDefs
'Check Recordsource
If Forms(docContainerName.Name).RecordSource = qdfGetQueryNames.Name Then
DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Form Record Source';")
End If
lngSLine = 0
lngSCol = 0
lngELine = 0
lngECol = 0
'Check Procedures
If mdlFormModules.Find(qdfGetQueryNames.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then
DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Form VB Procedure';")
End If
Next qdfGetQueryNames
DoCmd.Close acForm, docContainerName.Name, acSaveNo
Next docContainerName
'Check Report Recordsource & Procedures
For Each docContainerName In ctrReports.Documents
dbs.QueryDefs.Refresh
DoCmd.OpenReport docContainerName.Name, acDesign
Set repGetActiveReport = Reports(docContainerName.Name)
Set mdlReportModules = repGetActiveReport.Module
For Each qdfGetQueryNames In dbs.QueryDefs
'Check Recordsource
If Reports(docContainerName.Name).RecordSource = qdfGetQueryNames.Name Then
DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Report Record Source';")
End If
lngSLine = 0
lngSCol = 0
lngELine = 0
lngECol = 0
'Check Procedures
If mdlReportModules.Find(qdfGetQueryNames.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then
DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'Report VB Procedure';")
End If
Next qdfGetQueryNames
DoCmd.Close acReport, docContainerName.Name, acSaveNo
Next docContainerName
'Check Module Code
For Each docContainerName In ctrModules.Documents
dbs.QueryDefs.Refresh
For Each qdfGetQueryNames In dbs.QueryDefs
DoCmd.OpenModule docContainerName.Name
Set mdlModules = Modules(docContainerName.Name)
If mdlModules.Find(qdfGetQueryNames.Name, lngSLine, lngSCol, lngELine, lngECol) = True Then
DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, txtWhere_Used_Description, txtWhere_Used_Type) " & _
"SELECT '" & qdfGetQueryNames.Name & "', '" & docContainerName.Name & "', 'VB Module';")
End If
Next qdfGetQueryNames
DoCmd.Close acModule, docContainerName.Name
Next docContainerName
'Query is used by the database
dbs.QueryDefs.Refresh
For Each qdfGetQueryNames In dbs.QueryDefs
If Left(qdfGetQueryNames.Name, 4) <> "~SQ_" Then
strCheckQueryIsUsedByDatabase = "Not Used"
strCheckQueryIsUsedByDatabase = DLookup("[txtQuery_Name]", "Application_Query_Where_Used_Analysis", "[txtQuery_Name]='" & qdfGetQueryNames.Name & "'")
If strCheckQueryIsUsedByDatabase = "Not Used" Then
DoCmd.RunSQL ("INSERT INTO Application_Query_Where_Used_Analysis ( txtQuery_Name, fQuery_In_Use_By_DB ) " & _
"SELECT '" & qdfGetQueryNames.Name & "', False;")
End If 'strCheckQueryIsUsedByDatabase = "Not Used" Then
End If 'Left(qdfGetQueryNames.Name, 4) <> "~SQ_" Then
Next qdfGetQueryNames
dbs.Close
Set frmGetActiveForm = Nothing
Set mdlFormModules = Nothing
Set docContainerName = Nothing
Set ctrForms = Nothing
Set ctrReports = Nothing
Set ctrModules = Nothing
Set dbs = Nothing
End Sub
Hope that helps
Allan
