Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Earth | North America | United States | California | Long Beach
    Posts
    62

    Unanswered: Determine Queries in Use

    Is there a way to find out which queries are being utilized by reports or forms?

    I have inherited several databases with many queries and I would like to clean them up (i.e. remove any temporary queries from the database). However, I do not want to delete a query that is needed somewhere.

    I also do not want to open every report and form to see which queries are being utilized. This is because there are reports that use queries that also use other queries.

    Any ideas or help is greatly appreciated.

    Jack

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I've done this before, unfortunately I can't find the code.

    You need to build some code that:
    1) loads each querydef.name into an array.
    2) for each form
    a)check recordsource against array. If there is a match,
    delete that name from the array.
    3) for each report
    a) same as above
    4) Load remaining records in array into a temporary table. That will give you the name of all queries that are not used as a recordsource for a form or report.

    CAUTION: This will not check for queries that are used in modules or used by DLOOKUP type functions.

    The code is not very long, and will run quickly.
    Inspiration Through Fermentation

Posting Permissions

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