Chapter 27 Search and Replace in Queries
Chapter 27 Search and Replace in Queries
S
tructures of queries in large Access applications can become very complicated,
particularly when queries are layered on top of each other. There may be over one
hundred queries in a database and it is difficult to keep track of which tables or
subqueries they use.
If a table name or field name is changed, which is a strong possibility if you are linking to
external tables, it can be a nightmare finding the queries that this affects. Tables in an Access
database can also become redundant due to changes in the user requirements of an application.
Which queries do they affect and which queries is it safe to make redundant?
As a developer, you can write some VBA utilities to help you search queries.
Searching for a Specific String Within All Queries
You will frequently come across situations where you need to find all the queries that use a
specific table, subquery, or field name.
Run this code on the Northwind database. You can access this database by loading Access
and then clicking Sample in the Navigation pane on the left-hand side of the opening screen.
Double-click the Northwind icon.
First you need to create a small table to hold the results, since in some applications this
can cover many query names.
297