Append Query in Microsoft Office

Maker QR in Microsoft Office 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

I have found that if problems occur, it is worth splitting the Append query into several small sections using criteria so as to narrow down which section has failed Narrowing down the failed section further usually reveals what the problem is Because it does not return records, it is run as a command In its simplest form, it is:

INSERT INTO NewCustomers SELECT * FROM MyTable;

This appends all the data in the MyTable table into one called NewCustomers It does not delete records, so every time it is run the NewCustomers table will keep growing You can also use the following criteria:

INSERT INTO NewCustomers SELECT * FROM MyTable where CustomerName="Richard"

This assumes that MyTable has a field called CutomerName This will add new records into the NewCustomers table from the MyTable table where the CustomerName field is equal to Richard You can also specify which fields you want to append into the new table:

This assumes that MyTable has fields called CutomerName and Company and that the NewCustomers table has the same The field names can be different in the destination table, but this must be reflected in the SQL query Also, it is essential that the data types are the same in each field in each table They can also be in a different order in the destination table, but again they must be specified in the SQL query so they are in identical order to the insert and select part of the statement You can now see that this type of query can become very complicated and can easily go wrong! 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 Append query icon in the Query Type group of the ribbon It is a good idea to look at the results in a data sheet view (by clicking the View icon in the Results group of the ribbon) before you run the query This will show you what will be appended without actually enacting the query If anything is wrong at this point, you have not damaged the database

To run this from within VBA, you can use the DoCmd object (see 16 for more information on this object):

Sub MakeTableQuery() DoCmdSetWarnings False DoCmdOpenQuery "MyAppendQuery" DoCmdSetWarnings 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 the Execute method This can be done by either calling the query or the actual SQL: