Replace DAO code loops with the equivalent SQL statements

Replace DAO code loops with the equivalent SQL statements - but not always

As a general rule, it is better to use a single stored query than to write the equivalent code in DAO. All the reasons given in tip #7 apply, except even more so when it comes to maintainability. However, as the sample code demonstrates, there are scenarios where the DAO code will be faster than the equivalent Jet 2.0 query. In particular, if you're doing a simple single table update it may be quicker to use DAO code rather than using a SQL query. This will only be true if:

You can open the table as a table-type recordset.
You are not dealing with ODBC data.
You must use transactions.
You are not updating a large number of fields.
It is a simple, single table update (i.e., not a joined update).

In the above case, you may wish to experiment with using code instead of a SQL statement - knowing that you give up the maintainability of a stored query in doing so.