Unused queries

[avtuvy]

I inherited a large Access project, the Query menu displays many queries and I have a feeling that many of them are not used, is there a way to identify those unused queries?

 

[vbaInet]

I don't think there's a "Date Last Used" or "Date Last Run" property of an object (i.e. query in your case). The only dates that are stored are Date Created and Date Modified.

I suppose you could send a survey asking stuff which reports they use often and from the survey you could decipher which ones are frequently used.

 

[Guus2005]

You could left click on a query and select "Object Dependencies" or you could use the code below to search a string in all queries

Public Sub SearchInQueryDefs(strSearch As String)

   Dim qdf            As QueryDef
   Dim qdfs           As QueryDefs
   Dim strMes         As String
   Dim blnFound       As Boolean
   Dim strFormname    As String
   Dim strControlName As String
   
   On Error GoTo Err_SearchInQueryDefs
   
   Set qdfs = CurrentDb.QueryDefs
   
   For Each qdf In qdfs
      blnFound = InStr(1, qdf.SQL, strSearch) > 0
      If blnFound Then
         Debug.Print "Searching : " & qdf.Name & "...";
         Debug.Print " - found"
         If Left$(qdf.Name, 1) = "~" Then 'Form Control rowsource query
            strFormname = Mid$(GetPart(qdf.Name, "~", 2), 5)    'removing ~sq_c
            strControlName = Mid$(GetPart(qdf.Name, "~", 3), 5) 'removing ~sq_c
            strMes = "Found!" & vbCrLf & vbCrLf & "" & strSearch & " found in " & vbCrLf & " Form: " & strFormname & vbCrLf & " Control: " & strControlName & vbCrLf & vbCrLf & qdf.SQL & "" & vbCrLf & vbCrLf & "Click 'Yeah' to continue search, 'Duh' to stop"
         Else
            strMes = "Found!" & vbCrLf & vbCrLf & "" & strSearch & " found in " & qdf.Name & vbCrLf & vbCrLf & qdf.SQL & "" & vbCrLf & vbCrLf & "Click 'Yeah' to continue search, 'Duh' to stop"
         End If
         If vbNo = MsgBox(strMes, vbExclamation + vbYesNo, "SearchInQueryDefs") Then
            Exit Sub
         End If
      End If
   Next qdf
   
   MsgBox "Done searching.", vbInformation, GetAppTitle()
   
Exit_SearchInQueryDefs:
    Exit Sub
Err_SearchInQueryDefs:
    Debug.Print "Error Searching : " & qdf.Name & "..."
    Resume Next
   
End Sub
Public Function GetPart(strString As String, strSep As String, intPart As Integer) As String
   Dim intFound As Integer
   
   intFound = InStr(1, strString, strSep)
   If intFound > 0 Then
      If intPart = 1 Then
         GetPart = Mid$(strString, 1, intFound - 1)
      Else 'intPart > 1
         GetPart = GetPart(Mid$(strString, intFound + 1), strSep, intPart - 1) 'recursive
      End If
   Else 'intFound = 0, no occurence of seperator so return complete string
      GetPart = strString
   End If
   
End Function

Rick Fisher(rickworld.com) developped a Find and Replace tool or you could use Mztools.

Enjoy!