Need efficent method for finding "query within query"

[skydivetom]

Experts:



I need some general assistance with Access database management.

Background - General:
- Attached is a sample database which contains a few sample tables, 4 queries, 4 forms.
- My actual database, however, is much larger and includes roughly 100+ queries and about 20 forms
- These 100+ queries and ~20 forms have been developed over the last couple of months.
- At this time, I am going through a "cleanup" process. That is, I'm trying to which queries are a) required vs. b) not requred by any forms/reports/VBA, etc.

Background on Forms/Reports:
- As a general rule, I never utilize the query name in any of my forms and/or reports.
- That is, if a query is the source for a form or report, I always utilize the actual SQL as my rowsource. In my view, this reduces the number of query objects in my database.
- However, in both form and reports, I **do** utlize the query name (e.g., Query1) inside my VBA.

Now, before I delete any **potentially** unnecesasary query, I utilize a "Find" across "Current Project" (see attached "FindQueryinVBA.jpg"). Naturally, if the query is found, it won't be a candidate for deletion.
Given that I only have about 20 forms, conducting a "Find" is relatively straight-foward as does NOT take too much time.

However, some of my queries are part of other queries (i.e., Query1 may serve as an input to Query2). More specifally, in the attached sample database, "Q_Step4" is part of my query "Q_Step3". Naturally, if I had deleted Q_Step4, Q_Step3 would no longe work.

So, while it's easy to determine whether or not a query is used in my Forms' VBA, I haven't figured out a way how to "find a query within a query". Sure, I could open any of the 100 queries and copy the SQL into an ASCII program and the search for it; however, I'm afraid that would take a signficant amount of time and potentially opens me for making errors.

Thus, my question: Is there an efficient way to search the database and determine which query is utilized in another query so that I won't accidentially delete a required object? If so how?

EEH

[Micron]

First, delete nothing - rename to "zz"+the existing name. If you show all objects in the nav pane as I do, zz pretty much guarantees that it drops to the bottom of the list where I ignore it for a time. At some point after testing most/all of the processes, I figure it isn't needed so then it goes. This might be same day or weeks later. If something breaks, it's usually obvious which of the renamed objects is the culprit based on the error message raised, and so the zz gets removed and you're back in business.

Second, there is the database documenter and with it you can look at all properties or just the data related ones. All properties with your db is likely to generate a 50 page report, so start with the data. You would have to read carefully but this should expose that query1 depends on query2. While this is also possible to see using the dependencies tool, that will not show you that cmbCustomer on form frmCust relies on a query or a lookup in txtName relies on a query field for example. From reading your post I'd say there's no need to reinvent the wheel.

[skydivetom]

Micron - using the "zz" prefix is an excellent idea. I will start there before deleting any queries.

Thanks for the excellent advice.

Cheers,
Tom

[Micron]

You're welcome. Glad I could help.
Try the documenter if you're not familiar with it.

[skydivetom]

Yes, I definitely will check it out.