Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List the queries to a file (Access XP)

    How can I get a list of all my queries which I can insert into a document ? I cannot see any way - anyone any ideas ?

    Cheers

    Alan

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: List the queries to a file (Access XP)

    In Access, Select Tools / Analyze / Documenter, Select the Queries Tab and check (or select all) for the queries you want to see. Is this what you are looking for?
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Lounger
    Join Date
    Dec 2002
    Location
    Rochdale, Gtr Manchester, England
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List the queries to a file (Access XP)

    As ever - this list comes up trumps. Thanks all - I've got what I need.

    Alan

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: List the queries to a file (Access XP)

    Hans,

    Neat Code. I never thought of doing it this way. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> Once again, thanks for the new info.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List the queries to a file (Access XP)

    Small typo. The AllQueries collection belongs to the CurrentData or CodeData objects, not to CurrentProject. The line should read:

    For Each obj In CurrentData.AllQueries
    Charlotte

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

    Re: List the queries to a file (Access XP)

    Thanks, I have corrected it.

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

    Re: List the queries to a file (Access XP)

    <P ID="edit" class=small>(Edited by HansV on 12-Feb-03 08:04. Changed CurrentProject to CurrentData - thanks to Charlotte for pointing this out.)</P>You can loop through the CurrentData.AllQueries collection:

    Sub ListAllQueries()
    Dim obj As AccessObject
    ' Search for AccessObject objects in AllQueries collection.
    For Each obj In CurrentData.AllQueries
    ' Print name of obj.
    Debug.Print obj.Name
    Next obj
    Set obj = Nothing
    End Sub

    This will list all query names in the Debug window; you can copy it from there into a document. You can also adapt the code to write the names to a text file.

    Or you can use DAO to list the QueryDefs in CurrentDb (you need to set a reference to the Microsoft DAO 3.6 Objecty Library for this):

    Sub ListAllQueries()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    ' Search for QueryDef objects in QueryDefs collection.
    For Each qdf In dbs.QueryDefs
    ' Print name of object.
    Debug.Print qdf.Name
    Next qdf
    Set qdf = Nothing
    Set dbs = Nothing
    End Sub

    Finally, it is also possible to create a query based on MSysObjects:

    SELECT Name FROM MSysObjects WHERE Type = 5

Posting Permissions

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