As said previously, do not forget to set the warnings back again. If you do not, you will
receive no warnings at any point within your Access database. If, for example, you decide to
edit a query and then close it, you will no longer get the warning message “Do you want to
save this?” It will default to “Save,” which could be disastrous if you have made changes that
no longer work!
You can also run this by using the Execute method. This can be done by either calling the
query or the actual SQL:
CurrentDb.Execute " UPDATE MyTable SET CustomerName = 'Richard'"
If your query string is particularly long and complicated, you may find it easier to run it
over several rows of code. The problem is that you cannot use the continuation character
within a string.
To get around this, use a string variable to concatenate your query together:
Src = " UPDATE MyTable SET CustomerName = 'Richard' "
Src = Src & "WHERE (((CustomerName)='Shepherd'))"
CurrentDb.Execute Src
If you need to use quotes for a string criteria within your query string, use single quote
marks instead of double (this will not cause the error of double quote marks).
Pass Through Query
The Pass Through query is a special query for interaction with external databases. It is
discussed in Chapter 19.
Using Custom Functions within Queries
When you create a query, you often come across a situation where you need to do something
to a field, but there is no easy way of doing it in SQL.
To get around this, you can write a function in VBA and then incorporate it into your SQL
query.
An example (which I have come across in the commercial world) might be that in your
query, if a particular field value is all in uppercase, then another value field must be zero,
otherwise it has its numeric value showing.
Though this is rather strange stuff, you would have huge problems doing this in a SQL
query in Access. The solution for that was to write a function that checked a string to see if it
was all in uppercase:
Function CheckUpperCase(Target As String) As Boolean
Dim Flag As Integer
For n = 1 To Len(Target)
Chapter 12: SQL Queries 163