Solved Search all objects & properties for specific sql string

[adhoustonj]

Hello AWF,
Does anyone have any experience or code with searching all database objects for a sql string? I've started looking at how to approach this over the last few days and haven't come up with much.. Other than the export to text and then search through text files.

Basically - I've migrated some back end databases to SQL Server, and I'm noticing one query that is an outlier when viewing the top resource consuming queries on the server, but I can not find the query in my front end app! So I've scratched my head a few times, and rather than go one by one searching each object, I was hoping to be able to search all via vba.

 

[ebs17]

There are analysis tools like ...

ACCESS Dependency Checker - Overview

 

[Minty]

The deep search function in V-tools will find almost anything.

V-Tools

 

[adhoustonj]

I will check both of those out. Thanks!

 

[GaP42]

Not as sophisticated, but I have a routine to generate the list of all queries in the database to a table "T_SysSQLCollection" at the time of launching the report.
The table:

The report

The queries cover those embedded in forms, subforms, reports and queries
I have not set up a search SQL capability but easily done.

Public Function ListQueries()

'---------------------------------------------------------------------------------------
' Procedure : ListQueries
' DateTime  : 2020 - Sep - 16
' Author    : Graham Pegler
' Purpose   : To create documentation for all queries/as SQL in this database - populates table to hold the name and SQL _
'               of each query, before generating a report preview to incorporate in system docs.
'
'**** Note: This routine deletes all records in the table t_SQLCollection on each execution before regenerating the records ****

Dim db As DAO.Database
Dim rst As DAO.recordSet
Dim qdf As DAO.QueryDef

Dim i As Integer
Dim QueryName, QName, strQryDel As String

On Error Resume Next

' SQL to clear records
strQryDel = "DELETE t_SysSQLCollection.* FROM t_SysSQLCollection;"

On Error GoTo Err_ListQueries

'   Clean out the existing records before repopulating

Set db = CurrentDb()

db.Execute strQryDel, dbFailOnError
DoEvents
        
Set rst = db.OpenRecordset("t_SysSQLCollection")
i = 0
With rst
    For Each qdf In db.QueryDefs
    .AddNew
        !QueryName = qdf.Name
        !qSQL = qdf.sQL
        !qDescription = qdf.Properties("description")
        .Update
        i = i + 1
    Next
End With
    
MsgBox "Finished - " & i & " Queries have been documented", vbInformation, "QUERY LIST"

rst.Close
db.Close