To run this from within VBA, you can use the DoCmd object (see Chapter 16 for more
information on this object):
Sub MakeTableQuery()
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyAppendQuery"
DoCmd.SetWarnings True
End Sub
Notice that the DoCmd object has been used to switch the warning messages off and then
switch them back on afterwards. This is because by running an Append query the warning
message will normally be displayed to tell you that you are about to delete records. If you are
running this as part of a VBA procedure, you will not want this message appearing and
halting the execution of your code.
As stated earlier, 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 theExecutemethod. This can be done by either calling the
query or the actual SQL:
CurrentDb.Execute " INSERT INTO NewCustomers SELECT * FROM MyTable
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 = " INSERT INTO NewCustomers "
Src = Src & "SELECT * FROM MyTable"
CurrentDb.Execute Src
If you need to use quotes for a string criterion within your query string, use single quote
marks instead of double (this will not cause the error of double quote marks).
Update Query
The Update query updates records based on the query to a specified value. The data you are
updating must conform to the rules of the table. Null values cannot be updated into required
fields and data types must correspond, otherwise errors will occur.
Chapter 12: SQL Queries 161