Microsoft Access VBA Macro Programming

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:

INSERT INTO NewCustomers ( Company, CustomerName )

SELECT Company, CustomerName

FROM MyTable

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.

160 Microsoft Access 2010 VBA Macro Programming