CODEWITHSUNDEEP
databasems-accessautomationvba
Prasenjit Chatterjee
Prasenjit Chatterjee

Reputation: 99

how to list down or enumerate all the Query names under a MS Access Macro using VBA

When we open a Macro in MS access in Design mode, we see all the queries (OpenQuery) and many other events can be there.I need to list down only the query names inside a macro. I know how to get all query names from the DB already, what I am specifically looking is, if I pass a macro name inside a function/sub in VBA it will list down only those queries inside it.

Like in the following image , there are three queries in the database. But "Macro1" packs two queries, so, if I want to get only those two query names by passing Macro1 as a parameter string in a function.

Sample image

Upvotes: 1

Views: 2079

Answers (1)

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

Thanks to @SergeyS. for pointing out the source code is in Access Dependency Checker! I've used that tool for years and had never noticed...

I condensed the code found there to export each macro as a txt file. To run this, place this module in your database, change the 'sMacroFile' path to suit your environment.

And a huge thanks to Thomas Koester for providing Access Dependency Checker (http://www.accessdependencychecker.com/)!

Option Compare Database
Option Explicit

Function Process_Macros()
Dim mcr As Object
Dim sMacroFile  As String
    If CurrentProject.AllMacros.Count > 0 Then
        '--- cycle thru collection of macros
        For Each mcr In CurrentProject.AllMacros
            Debug.Print "Macro: " & mcr.Name
            sMacroFile = "c:\TEMP\" & mcr.Name & ".txt"
            ''''Call readMacro(ac, mcr)
            SaveAsText acMacro, mcr.Name, sMacroFile
        Next mcr
    End If
End Function

Upvotes: 3

Related Questions