• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

How to get text of all access queries

I have an access db that is very complicated someone before me wrote.  They have several queries, number easily a couple hundred.  I'm trying to find a specific keyword within the SQL text of a query, but I don't know which one.  Is there a way I can export all queries, or search the contents of them for specific text?

Using access 2010
3 Solutions
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Must have this tool - all time BEST Find & Replace:


Will search all your queries to find that text:


Here's some code that will ouput the sql. Also, if you uncomment the middle for/next loop it will list the field names in each query.



Public Sub MyQueries()
    Dim i1 As Integer, i2 As Integer

On Error Resume Next

    For i1 = 0 To CurrentDb.TableDefs.Count - 1
        'query name
        Debug.Print CurrentDb.QueryDefs(i1).Name
        'For i2 = 0 To CurrentDb.QueryDefs(i1).Fields.Count
        '    'field names
        '    Debug.Print CurrentDb.QueryDefs(i1).Fields(i2).Name
        'Debug.Print ""
        Debug.Print "SQL: " & CurrentDb.QueryDefs(i1).SQL
End Sub

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 I'll second MX's comment; best (cheap) money you'll spend on a development tool.  And besides the find and replace, the cross reference utility in there is a god send when trying to cleanup someone else's DB.

  Rick has really never gotten the praise he deserves for that tool...

You can go through a bit of manual work if you'd like:
     Go to the Database Documenter (on the Database Tools tab in MS 2007)
On the Queries Tab, hit the select All button (to place a check by all of your queries)

I'd recommend going into options and turning all off except for SQL... you really only want to see the SQL syntax.

After you select OK...a report will be generated....right click....export to Word and search for your keyword.

Good luck
UnderSevenAuthor Commented:
I think I'll use all of these.  I'm selecting the database documentor as the best simply because it uses built in functionality.  Thanks.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now