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?
A:
JET is the database engine used by Access. Or put it another way, Access is an app built on the JET database engine. (Access doesn't have to use JET, it can also connect directly to SQL server.)
When ever you execute queries against an .mdb file, regardless of technology, it is the JET engine that executes the query.
A version of JET is needed to access a .mdb, but wether it has to be 4.0 will depend upon the .MDB file version and whether there are prevalent bugs in earlier versions that you need to avoid. But in general, yes, you should expect to use the latest JET version with an Access MDB.
OUTER JOINs work on older versions - the bug mentioned is only prevalent when there is also a WHERE clause.
mdma
2010-05-01 20:03:53
Your answer seems to contains a contradiction: You say "Access doesn't have to use JET", but then you say "When ever you execute queries against an .mdb file, regardless of technology, it is the JET engine that executes the query."
Craig Johnston
2010-05-02 01:14:14
No contradition - when Access is used against SQL server, you create a .ADB file, not a .MDB file. .MDB is always JET.
mdma
2010-05-02 01:41:47
There is no such thing as an "ADB" file in Access. There is an ADP file (Access Data Project), which connects direct to SQL Server via ADO. But you don't need to use an ADP to work with SQL Server. You can use an MDB or ACCDB and connect via ODBC (this is the method MS recommends -- ADPs have been deprecated in favor of MDB/ODBC except for reporting for nearly 5 years now).
David-W-Fenton
2010-05-03 21:55:04
Sorry, it was a typo - I meant ADP. My point was to illustrate that not everything goes through the internals of the JET query engine.
mdma
2010-05-03 22:20:17
But not going through Jet is a *drawback*, because Jet is so smart and efficient. ADO has terrible problems (like bypassing views and going direct to the underlying tables for otherwise unupdatable DML commands) and ADPs turn out to have more layers between them and the data than MDBs with ODBC. This is why MS has been deprecating ADPs in favor of MDB/ODBC for about 5 years now.
David-W-Fenton
2010-05-07 00:56:08
A:
EDIT: I just reread the KB article, and it says the problem is with Jet 4.0 Service Pack 3 and earlier -- that's a version of Jet that has long, long ago been superseded. There should be no Windows 2000 or later computers anywhere that have Windows Update turned on or that have been updated since c. 2002 that have any version of Jet as old as service pack 3. To check the version of Jet on your computer, find MSJET40.DLL. The build number begins with the service pack number. On this PC, I have version number 4.0.9511.0, so that's SP9.
If you have an MSJET40.DLL with version number 4.0.3XXX.X or earlier, then follow the instructions on getting the service pack, and the problem will be resolved. But I don't believe there's any signficant number of Windows PCs out there that still have Jet 4.0 SP3 or earlier on them. And only PCs that aren't being updated with Windows Update could possibly have it.
ORIGINAL ANSWER: Jet 4.0 was released in 1999 with Access 2000. It was included in in Windows 2000 and is included in every version of Windows since. Because it's an OS component (it is used for interaction with the Active Directory data store, though AD uses the other Jet engine, not the one used by Access). Jet 4.0 is a frozen version of Jet and will not be developed any further by the Windows team (who owns the code for it).
When Access 2007 was released, a new version of the Jet database engine was created, private to the Access development team, and called ACE. It is really Jet 5.0 (or 4.5, depending on how major you consider the enhancements to be), but it introduced a new file format, ACCDB.
The article you cite says it applies to only Access 2002 and Access 2003, but I'd think that since it's a Jet 4.0 issue, it would also apply to Access 2000. Likewise, it would apply to any use of Jet 4.0 from outside Access, e.g., OLEDB or ODBC.
Note that via DAO, you can't encounter the problem, because DAO cannot use SQL 92 compatibility mode. The problem will always occur with OLEDB because the Jet driver for OLEDB always uses SQL 92 mode.
It's not clear to me if the problem is avoided if you use ACE to query an MDB.
David-W-Fenton
2010-05-02 00:58:07
I don't accept your argument that because DAO cannot use SQL 92 compatibility mode that the problem won't occur. The article is simply saying that SQL-92 compliant queries (meaning those that comply with the language standard) are susceptible.
Craig Johnston
2010-05-02 01:52:21
I beg to differ. The SQL that is given as a workaround for the problem uses SQL 89 syntax for the subselect, i.e,. "SELECT [...]. As Alias" -- that's provided as the workaround, so it would seem to me that this implies that using SQL 89 avoids the problem, and since DAO cannot use SQL 92, then DAO avoids the problem. It's all moot in any event, since the entire problem applies only to an extremely outdated version of Jet 4.0.
David-W-Fenton
2010-05-02 21:18:50
With DAO I can hit the MDB with any SQL statement I like - it's up to DAO to return an result or error. I don't understand what you mean when you say "DAO cannot use SQL 92".
Craig Johnston
2010-05-04 01:27:12
Pass DAO SQL 92 syntax, e.g., LIKE "Fent%" and it will not give you a correct result. ADO, on the other hand, uses SQL 92 by default and won't give correct results with SQL 89 syntax, e.g., LIKE "Fent*".
David-W-Fenton
2010-05-07 00:53:53
OK, I'll take your word for it, however a SQL92 compliant query is also SQL89 compliant isn't it? Therefore if DAO is given a query compliant with both SQL92 and SQL89 then the article applies to such a query since it is SQL92 compliant. Isn't the query in the article also SQL89 compliant?
Craig Johnston
2010-05-07 05:34:07
SQL 92 compatibility mode in Access is not the same thing as SQL92 compliant. Access's version of SQL 89 is not the same as complying with the SQL 89 standard. Yes, it's confusing, but it's also what you run into in every database engine, i.e., that each one says its SQL dialect conforms to the standards, yet every one varies in important ways. SQL 92 mode was introduced into Access impliciatly with the introduction of Jet 4 and ADO in Access 2000. It was introduced into the Access application explicitly with A2002 or A2003, and is there only to provide better compatibility with SQL Server.
David-W-Fenton
2010-05-07 18:43:46
+1
A:
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.
Thomas
2010-05-02 03:09:09
@Craig Johnston - Actually, the solution would be to used the newest version of Jet you can (or SQL Server Express). Jet 4.0 is over 10 years old, so I would not recommend starting any new project with something that old unless I was given absolutely no other choice. It is not mandatory to use Jet with Access btw. You can create solutions against SQL Server that might use a MDB file on the frontend but do not use the Jet engine for data on the backend.
Thomas
2010-05-02 03:26:27
@Craig Johnston - To be more specific, it is not required to use Jet as the backend database storage technology with Access as the frontend client application technology. You can create Access frontend solutions that use something other than Jet (i.e. something other than a MDB file) as the backend store.
Thomas
2010-05-02 03:27:44
My code is already embedded with DAO calls, so I think I am stuck with JET. I need to know whether I should upgrade the JET dlls to v4.0.
Craig Johnston
2010-05-02 03:39:57
@Craig Johnston - Ah. So I take it that the current engine is Jet 3.5 or something? Either way, I would still recommend upgrading to the newest version of the engine you can because of other bug fixes and support. So, yes I would upgrade to 4.0. If the above query in the KB is a problem, there is a workaround that they mention in the article. TBH, any time you write any query that cannot be rendered in the QBE, you are playing on the knife's edge of Jet's capabilities and that has been true since the first version..
Thomas
2010-05-02 03:53:20
I explain the versions of Jet in my reply below. Jet 3.5 dates from 1996! Jet 4 was first released in 1999! The cited Knowledge Base refers to a very old version of Jet 4, i.e., Jet 4.0 service pack 3 and before. That dates the problem to c. 2002-03. No Windows PC should have that old a version of Jet 4 installed on it, because it's an OS component and updated by Windows Update. A recently patched PC should have Jet 4 SP9, which will not exhibit the problem.
David-W-Fenton
2010-05-03 21:53:10
Your point about the age of the various JET engines is understood and you have stated it several times. However, if an application is deployed with msjet35.dll then this engine will be on the machine and it will be used by that application.
Craig Johnston
2010-05-07 07:24:21
@Thomas: My question was not whether JET needs to be used at all, but rather whether if you are using JET do you need to use v4.0 SP4 or above?
Craig Johnston
2010-05-07 07:26:44
@Craig Johnston - If you are using Jet 4.0, IMO, there is no reason not to use the latest patch unless there is something specific in one of the SP4+ patches that is causing a break. If for some reason, the machine on which the app is running is so old that SP4 is the last patch that can be run and the application works fine, then there is no reason to patch it until they put it on a newer OS. Beyond that, I cannot fathom would would hold you back from using the latest Jet patch.
Thomas
2010-05-07 15:16:31
If you're using Jet 3.x, then issues in Jet 4 are irrelevant. The Knowledge Base article doesn't have anything to do with Jet 3.5x, and had you said that you were using Jet 3.5x at the beginning, answering your question would have been a lot simpler and would have avoided all the massive confusion that has ensued.
David-W-Fenton
2010-05-07 18:45:34