This assumes that MyTable has a field called CutomerName. This will create a new table
called MyNewTable, which only contains records when the CustomerName field is equal to
Richard.
You can use the Query Design window to build a Make Table query. It is the same as the
earlier Select query, but you must click the Make Table query icon in the Query Type group
of the ribbon.
To run this from within VBA, use the DoCmd object (see Chapter 16 for more information
on this object):
Sub MakeTableQuery()
DoCmd.SetWarnings False
DoCmd.OpenQuery "MyMakeTableQuery"
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 afterward. This is because by running a Make Table 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.
Again, do not forget to set the warnings back as they were. 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 "SELECT * INTO MyNewTable FROM MyTable;"
Append Query
The Append query appends records based on the query into another table. The data you are
appending must conform to the rules of the new table. Filed data types must correspond and
required fields must be provided, otherwise errors will occur.
When this type of query goes wrong, it is one of the hardest to debug in terms of trying to
find out why certain records are not acceptable to the new table. Error records turn up in a
new error records table, but even then it can be quite difficult to locate what is causing the
problem, especially if there are many fields.
Chapter 12: SQL Queries 159