"Posting" queries are faster than explicit code

The Microsoft Jet database engine provides many advanced query capabilities. Many of these are overlooked when it comes time to write update code. One example of a common scenario where a single update query can replace multiple lines of code is a so-called "Posting" query.

A posting query typically consists of two tables: a table that contains the "master" data, and an "update" table of similar structure that contains changed values for existing data in the master table and new rows to be inserted. Usually you would write explicit code that walks the update table and applies the changes to the master table, either updating or appending new records as required.

The code to do this can be replaced with a single query. By taking advantage of Jet's ability to do updatable outer joins, you can join the update table to the master table and specify that the master fields be updated with the update fields. If a record doesn't exist in the master, then because it is an outer join, the new records will appear in the candidate update set and the join field will be updated - thereby creating a new record.

For example, the following query would update to an Orders table from an Order Updates table:

UPDATE Orders RIGHT JOIN [Order Updates]
ON Orders.[Order ID] = [Order Updates].[Order ID]
SET Orders.[Order ID] = [Order Updates].[Order ID],
Orders.[Customer ID] = [Order Updates].[Customer ID],
Orders.[Employee ID] = [Order Updates].[Employee ID],
Orders.[Required Date] = [Order Updates].[Required Date],
Orders.[Shipped Date] = [Order Updates].[Shipped Date],
Orders.Freight = [Order Updates].[Freight];

This query could be tweaked further to allow for blank entries in the Order Updates table for certain fields. E.g. instead of:

Orders.Freight = [Order Updates].[Freight]

you could use:

Orders.Freight = IIF(IsNull([Order Updates].Freight), 
                    Orders.Fright, [Order Updates].Freight)