DAO and Jet

In Figure 13-1, you can see that Jet and DAO are intimately linked. They are not the same thing, however. Jet is a distinct component, even though only DAO and Microsoft Access know how to use it directly. Therefore, it’s not possible to use Jet directly. DAO exists primarily as an access method to Jet. However, as you will see, DAO is able to turn off some of Jet’s functionality when using an ODBC data source. This can sometimes have a significant effect on performance. Once again there is a trade-off, however, in performance vs. functionality.

Jet is driven by SQL, and the dialect of SQL that Jet speaks is different from the dialect used by other implementations. This fact has both benefits and drawbacks. When using DAO (and hence, Jet), it’s worth learning these differences because they can have a dramatic impact on performance. For example, in many database management systems (such as SQL Server), a join between two tables doesn’t have to be explicitly declared. SQL Server will interpret the following SQL statement as an inner join and optimize database access accordingly:

SELECT * 
FROM Employee, Dept
WHERE Employee.Dept_Id = Dept.Dept_Id

This same statement, when used via DAO and Jet, can have a completely different outcome. Rather than use the Dept_Id field to read the unique index in the Dept table, Jet will simply perform a cartesian join (that is, match each Employee record with all Dept records) and then throw away those that fail the criteria specified in the WHERE clause. This means that the number of records read is Count(Employee) * Count(Dept) (read every Dept record for each Employee record) rather than Count(Employee) * 2 (read one Dept record for each Employee record). If these were large tables, the difference in the elapsed time to execute the query could be dramatic.

But it’s not all bad news. Jet SQL also has some useful features. Perhaps the feature most widely used is its ability to perform cross-tab queries. For example, suppose you have a query that produces a list of sales by product and month. The output of such a query might look like this:

Although this is fine as far as it goes, it would be much nicer to see the query output in this format:

A cross-tab query is able to do exactly this kind of transposition, turning the values of a column in an ordinary select query into column headings in a cross-tab query.