Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Enumerate Queries (Access 2007)

    I've inherited an Access application with many queries. As a part of figuring out the application I thought a good first step would be to create a MyNotes table and use vba to dump the query names and SQL into the table so that I can add my notes as I analyze each. My problem is I have not figured out how to loop through all the queries. ADOX.Views only gives me the simple queries.

    Appreciate any help,
    Marty

  2. #2
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Enumerate Queries (Access 2007)

    Excuse me comming in, but thanks Hans, I found that very useful

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Enumerate Queries (Access 2007)

    Hans,

    Thnaks for a simple - clean approach. I finally built a sub and looped through dbs.AllQueries. For each query I called a function which first used Set cmd = cat.Procedures(pCommand).Command to try cmd.CommandText and on error 3265 switched to Set cmd = cat.Views(pCommand).Command. While it works, I like your approach much better.

    Thanks,
    Marty

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Enumerate Queries (Access 2007)

    The Database Documenter in the Analyze section of the Database Tools tab of the ribbon lets you generate a report that lists (customizable) details of database objects such as queries.

    If you want to fill a table:
    Create a table MyNotes with two fields:
    - QueryName (Text, length 64, primary key)
    - SQL (Memo)

    Create the following procedure in a standard module. It needs a reference to the Microsoft DAO 3.6 Object Library.

    Sub ListQueries()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("MyNotes", dbOpenDynaset)
    For Each qdf In dbs.QueryDefs
    ' Skip system queries
    If Not Left(qdf.Name, 4) = "~sq_" Then
    rst.AddNew
    rst!QueryName = qdf.Name
    rst!SQL = qdf.SQL
    rst.Update
    End If
    Next qdf
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •