Does the following article apply to all uses of JET (including from within client apps via DAO or OLEDB):
http://support.microsoft.com/kb/275058
Does this mean JET 4.0 or above is mandatory for anything involving an Access MDB?
Unfortunately, the KB article is worded poorly. The SQL 92 syntax to which they refer is when you add a non-column comparison filter in the ON clause of an OUTER join (which also means it will not be viewable in the QBE). Thus, a standard outer join (LEFT or RIGHT) with standard column comparisons to tables in the query will work just fine in all versions of Jet. It is when you try to add something other than column comparison filter (e.g. [Col] > 200) in the ON clause of an OUTER join that Access does not properly interpret the SQL statement.
I have personally run into this very issue, In short, just because it is standard SQL does not mean that Access will return the proper results. It is one of the many things that many people find frustrating about Access.
ADDITION To answer your last question, no, it is not mandatory to use Jet as the backend storage technology for an application developed with Access. You can create Access solutions that use SQL Server or other database products as the backend database store.
The Wikipedia article on the Jet Engine is actually pretty thorough in describing the differences and features.