How to get text of all access queries

Posted on 2011-04-21
Last Modified: 2012-05-11
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
Question by:UnderSeven
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    Must have this tool - all time BEST Find & Replace:

    Will search all your queries to find that text:

    LVL 18

    Assisted Solution


    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

    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)

     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...

    LVL 5

    Accepted Solution

    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

    Author Closing Comment

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now