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

Question

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.

I really appreciate dotNET, that you spent some time for my question to find a solution. But I am really sorry , if I am misunderstood, I already have enumerated all database objects whether its query or form or even list down all macro names. What i am struggling at is READING the Query names in a specific Macro" if i pass a specific macro name to a function , i only want to get the queries packed inside the macro, not any other macro. — Prasenjit Chatterjee
– Prasenjit Chatterjee, Commented Nov 10, 2017 at 16:13
Take a look at bytes.com/topic/access/answers/… that link suggest there is no way to directly view components of the macro using VBA, but it discusses how to export all macros to a text file, then parsing the text file. — Wayne G. Dunn
– Wayne G. Dunn, Commented Nov 10, 2017 at 16:39
Thanks a lot Wayne!! I think you are right, I've also seen this before posting here while searching the answer for myself. Still thought if anyone here can answer it in a different way. Actually I have several accdbs containing many macros and queries. I have a strict timeline to convert the scripts into SAS. Manually if I open each macro and then open the queries one by one to convert into proc sql. It will be too monotonous and time consuming. So I wrote all the codes to get sql from a query and enumerated all queries, but i needed to write them sequentially that are placed in a macro. — Prasenjit Chatterjee
– Prasenjit Chatterjee, Commented Nov 10, 2017 at 17:09
I did some searching but did not find a way to automate the export to text, or to automate the 'convert to Visual Basic' feature. If you have a large number of macros, ouch! — Wayne G. Dunn
– Wayne G. Dunn, Commented Nov 10, 2017 at 17:37
If I understood you correctly, you need to read query names from macroses. If so, it's definitely possible. At least accessdependencychecker.com does this using VBA, you can download and check how it was done, software is free. — Sergey S.
– Sergey S., Commented Nov 10, 2017 at 17:52

Wayne G. Dunn · Accepted Answer · 2017-11-10 20:11:09Z

3

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

answered Nov 10, 2017 at 20:11

Wayne G. Dunn

4,3322 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prasenjit Chatterjee Over a year ago

yes truly helpful!! Thanks a lot @Wayne and also @Sergey!! :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related