application—especially if you have to reinstate it on very short notice when an angry user
starts shouting.
A far better way is to rename the query or table as “zz” followed by the name of the table
or query. For example, the query qryMyQuery would thus be renamed zzqryMyQuery. All
the potential deletions then move to the end of the query list. Afterward, you should wait a
suitable period of time, such as a month, to make sure this has not caused any problems in the
operation of your application.
If a user runs into problems, you can reinstate the query quickly simply by removing the
“zz” from the name. If nothing untoward has happened over a period of time, you can delete
the query fully.
Search and Replace in a Query
Search and replace should only be run on one query at a time, since a blanket replacement
can create horrendous problems for your application if it goes wrong.
You will sometimes need to change queries if any field names are changed in linked table
queries, or if you have copied a query and wish to use it on a different set of tables.
One way to do this is to copy the entire SQL statement into a Word document using Copy
and Paste. You then use the Find and Replace function in Word, and then copy and paste the
SQL statement back into the query.
Since this can be time-consuming, you can write a small VBA utility to do it instead. Add
the following code into a module in the Northwind database. This code assumes you already
have a query set up called MyNewQuery.
Sub SearchReplaceQueries()
Dim QName As String, SearchStr As String, ReplaceStr As String
Dim Temp As String
QName = "MyNewQuery"
SearchStr = "Company D"
ReplaceStr = "Company B"
Temp = CurrentDb.QueryDefs(QName).SQL
For n = 1 To Len(Temp)
X = InStr(n, Temp, SearchStr)
If X Then
Temp = Left(Temp, X - 1 ) & ReplaceStr & Mid(Temp, X + Len(ReplaceStr))
End If
Next n
CurrentDb.QueryDefs(QName).SQL = Temp
End Sub
Before this code is run, it is advisable to make a copy of the query that will be changed.Chapter 27: Search and Replace in Queries 299