Query of Query where used in macros

[BillyB]

I want to build a query where I enter a query name a the query lists all the macros that contain that query. I have looked at the MSysObjects table but cannot determine how macros and queries are related. Any help will be greatly appreciated.

 

[pdx_man]

There really is no relation between Queries and Macros. Macros should be looked upon as a Pretty front end to VBA code. It's an easy interface to access basic functions. Kinda like the interface in writing queries. In the background, actual SQL is being written and THAT is what is executed.

Now, to your problem. I think what you want is to be able to type in a query name and return all of the macros that contain that query. Hmmm ... I don't think you can break down the Access Object Model to that kind of detail. I can't even imagine how it would be structured given Macros flexibility. An option might be to convert all of your Macros to VBA. To do this, right-mouse click on a Macro, and select Save As, then select the Save as Visual Basic Module option. This will create a Module object for that Macro. Do this for all Macros. Then, search the Modules for the text string (The query name, in your case) to see which Module contains it. Play with this code after you have all of your Macros converted to VB:

Public Sub FindInModule(MyStr As String)
    Dim dbs As Database, cnt As Container
    Dim mdl As module
    Dim lngSLine  As Long, lngSCol As Long
    Dim lngELine As Long, lngECol As Long
    Dim doc As Document
    
    Set dbs = CurrentDb
    Set cnt = dbs.Containers!Modules
    
    For Each doc In cnt.Documents
        DoCmd.OpenModule doc.Name
        Set mdl = Modules(doc.Name)
        If mdl.Find(MyStr, lngSLine, lngSCol, lngELine, lngECol) Then MsgBox doc.Name
        DoCmd.Close acModule, doc.Name
    Next

End Sub

Public Sub TestFind()
   'Put the text you want to search for in here
    FindInModule ("Sql_Str")

End Sub