CODEWITHSUNDEEP
ms-accessms-access-2013
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

How to search through Access macros?

My Access database has a query which, I suspect, is called by macros or other queries. Is there any way to run a Find on the "code" of all the macros and/or queries, to look for a text string (in this case, the query name)?

Upvotes: 1

Views: 1223

Answers (2)

Don George
Don George

Reputation: 1328

this lists all the tables & queries:

SELECT IIf([type] = 5, "Query", "Table") AS [Object type]
    ,MSysQueries.Flag AS [Query type]
    ,MSysObjects.NAME
    ,MSysObjects.Id
    ,MSysObjects.Type
FROM MSysObjects
LEFT JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
GROUP BY IIf([type] = 5, "Query", "Table")
    ,MSysQueries.Flag
    ,MSysObjects.NAME
    ,MSysObjects.Id
    ,MSysObjects.Type
HAVING (
        (
            (MSysObjects.NAME) NOT LIKE "~*"
            AND (MSysObjects.NAME) NOT LIKE "Msys*"
            )
        AND (
            (MSysObjects.Type) = 1
            OR (MSysObjects.Type) = 4
            OR (MSysObjects.Type) = 6
            OR (MSysObjects.Type) = 5
            )
        )
ORDER BY IIf([type] = 5, "Query", "Table") DESC
    ,MSysQueries.Flag
    ,MSysObjects.NAME;

and this one lists each object and the queries that reference it:

SELECT [~MSys Tables & Queries].NAME AS [Object]
    ,MSysObjects.NAME AS [Used in query]
FROM [~MSys Tables & Queries]
LEFT JOIN (
    MSysQueries LEFT JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
    ) ON [~MSys Tables & Queries].NAME = MSysQueries.Name1
GROUP BY [~MSys Tables & Queries].NAME
    ,MSysObjects.NAME
ORDER BY [~MSys Tables & Queries].NAME
    ,MSysObjects.NAME;

I don't know how to search through Macros the same way. Also, I'm not sure this always picks up objects used in Union queries.

I hope it gives you a place tro start.

Upvotes: 2

Sergey S.
Sergey S.

Reputation: 6336

You can install a free Access Add-in Access Dependency Checker, it can search strings thru all objects.

Upvotes: 0

Related Questions