List all MS Access queries in database

In my quest to optimize my Access queries, I wanted to view the underlying SQL for each query. However I have dozens of queries and manually examining each one is a hardship. Here's how I did it with VBA.

This code loops through the QueryDefs collection to list the SQL behind each query. Now I can check every one easily!

Sub ListAllQueries()
  Dim db As DAO.Database
  Dim queries As DAO.QueryDefs
  Dim query As DAO.QueryDef

  Set db = CurrentDb
  Set queries = db.QueryDefs

  ' loop through each query
  For Each query In queries
    Debug.Print "Name: " & query.Name & vbCrLf & "SQL: " & query.SQL
  Next query

End Sub
About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel