I am currently trying to design a process that can tell me which macros a given query can be found in. Unlike with tables, you can't use object dependencies to do this and in some of our dbs, there are so many macros, it is a time consuming business to look through them all to see if your query is used.

What I would like to do is loop through each macro and create a table that lists macro and then the queries used.
eg:
Macro Name -------- Query Name
Macro 1 --------- Q1
Macro 1 ---------- Q2
Macro 2 --------- Q1
Macro 2 ---------- Q3

If I then wanted to know which macros Q1 was used in, I could easily see it was used in both Macro1 and Macro2.

I had hoped that this information was stored in one of the system tables as I have used these for a previous task, but I can't find it anywhere. My other thought was to use the "Convert to VBA" function inbuilt into access to create a VBA script that I could then adapt into a list but I can't work out how to do this either.

If anyone has any thoughts or ideas on how I could create a list like this I would be v.grateful.

Thanks,
Mike

I have been able to find a function that claims to replicate the "Convert to VBA" Function and it is as follows:
DoCmd.SelectObject acMacro, strMacroName, True
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic

However, it always seems to end up creating an empty module or give an error message. Having googled it I have found the below code that exports it to a txt fle that works but is a bit cumersome. Have you any ideas how I could replicate this so that it just populates an external module?

Public Function subConvertMacros2TextFile()

Dim db As Database
Dim cnt As Container
Dim doc As Document
Dim prp As Property
Dim mdl As Module
Dim intFileNum As Integer
Dim strMacroName As String
Dim strFileName As String
Dim strModuleName As String
Dim strModuleText As String

Set db = CurrentDb
Set cnt = db.Containers("scripts")

For Each doc In cnt.Documents
strMacroName = doc.Name
strFileName = "C:\" & strMacroName & ".TXT"
intFileNum = FreeFile
Open strFileName For Output As #intFileNum

' Print the accessible macro-properties in which we are not interested...
For Each prp In doc.Properties
Print #intFileNum, CStr(prp.Name) & vbTab & CStr(prp.Value)
Next prp

Print #intFileNum, vbCrLf & vbCrLf & "MODULE TEXT : " & vbCrLf & vbCrLf

DoCmd.SelectObject acMacro, strMacroName, True
' push keyboard commands into keyboard buffer
' these keystrokes are needed for two dialogboxes
' of the ConvertMacrosToVisualBasic wizzard
SendKeys "%e", False 'disable generating errorhandling
SendKeys "%c", False 'press Convert button
SendKeys "{ENTER}", False 'press OK button
DoCmd.RunCommand acCmdConvertMacrosToVisualBasic

' the wizzard adds "Converted Macro- " to the macroname
strModuleName = "Converted Macro- " & strMacroName
DoCmd.OpenModule strModuleName
Set mdl = Modules(strModuleName)
strModuleText = mdl.Lines(1, mdl.CountOfLines)
Print #intFileNum, strModuleText

DoCmd.Close acModule, strModuleName, acSaveNo
DoCmd.DeleteObject acModule, strModuleName
Close #intFileNum

Next doc

db.Close
End Function 'subConvertMacros2TextFile