Talk:Access Database Engine

Possible sources

(not necessarily used in article)

Jet 2.0

• Microsoft Jet Database Engine 2.0: A User's Overview - MSDN

Jet 3.0

• Microsoft Jet Database Engine Programmer's Guide - Introduction
• Q&A: Microsoft Jet 3.0
• New Features in Microsoft Jet Version 3.0 - MS KB article 137039
• INF: Microsoft Jet 3.0 Questions and Answers - MS KB article 140210

Jet 4.0

• Description of the new features that are included in Microsoft Jet 4.0

Misc

• Jet Database replication (requires downloading of an EXE file)
• INFO: Identifying the Jet Database Engine Components

Non-MS articles

• Jet Engine: History - non-MS site
• History of VB - has a section on Jet

Security Bulletins

• Google search for security bulletins

Microsoft Jet Database Engine Programmer's Guide (first 4 chapters)

• [1] —Preceding unsigned comment added by 70.88.216.155 (talk) 00:44, 15 December 2010 (UTC)

Fox base

Wasn't some or the technology acquired from Fox?Rich Farmbrough 12:48, 23 July 2005 (UTC)

I believe that the Microsoft Rushmore query technology was developed from this. I think this info should probably go into that article. - Ta bu shi da yu 13:27, 23 July 2005 (UTC)

Picture

A picture of the cover of the Programmer's Guide? Weak. It's at times like these I really hate the stupid "OMG every featured article must have a picture or I object" rule that has developed... Maybe there's some really appropriate picture out there that really illustrates the engine, but I doubt it. And then, getting one that's properly licensed is probably completely out of the question, but we can wave our Fair Use wands over that. JRM · Talk 20:46, 25 July 2005 (UTC)

{{sofixit}}. If you don't like it, then you can remove it and add it to IFD. - Ta bu shi da yu 06:47, 10 August 2005 (UTC)

No, I'd rather appeal the rule than eliminate the image. I'm not quite quixotic enough to do that, however. And as for sofixit: I'll let you know if I ever trip over something more suitable. JRM · Talk 07:27, 10 August 2005 (UTC)

It's not a rule as FA articles don't necessarily need an image. I'll remove it and delete the image as I uploaded it. Thanks for looking out for an appropriate image though! - Ta bu shi da yu 07:43, 10 August 2005 (UTC)

Point me to an FA that has no image, please. I'd love to read the FAC nomination. :-) Don't hold your breath for a better image, though. It's unlikely something GFDL'able ever surfaces. Or anything, really; Jet doesn't even have a proper logo, as far as I know. Still, if even articles like ROT13 can get images, there should be hope. JRM · Talk 09:21, 10 August 2005 (UTC)

Access 2003

In the version table at the bottom right, Access 2003 is not listed. Presumably this is still Jet 4.0. Perhaps someone knowledgable can update the table accordingly.

Jet Red vs. Jet Blue

I'm surprised no one has brought up the differences between the two major implementations of the Jet engine, Jet Red (the engine used by Access) and Jet Blue (use by Exchange Server and Active Directory). I did some major research on this a while back. I will add it here when I get a chance.

Future of Jet

The Future section has two problems, seems to me:

• The claim that Jet from Access 2007 is only going to be supported in Access is not actually supported by the Eric Rucker citation (http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx). The truth is that the version of the Jet Database engine that the SQL Server team maintains will continue to exist, it just won't be updated. But that doesn't mean it's dead, because that's what we've been told about the Jet db engine since the release of Jet 4 back in 1999, that it was dead, with no further development. If you look at the cited blog post by Eric Rucker, you'll see that it doesn't support the claim being made.

• There is no citation on Microsoft's website for the section on the 32-bit issue. I've searched the website and have found nothing about it. I do know that there is a separate set of Jet DLLs with different names for 64-bit Windows Server 2003 (see http://support.microsoft.com/kb/870753/en-us). I don't know if those are 64-bit DLLs or not, but the fact that they have different names seems to me to indicate something specifically adapted to the 64-bit platform. Furthermore, the version of Jet that will be developed by the Access team will surely be compiled to run on 64-bit Windows natively. Indeed, I'd be surprised if that were not already the case, though I have no documentation for it.

Dwfenton 21:55, 31 October 2006 (UTC)

a modified form of an Indexed Sequential Access Method (ISAM) database

The records are not stored sequentially, and the system is not an ISAM system in the common sense of the word, ie not a system like Paradox or dBase.

So although the statement is technically correct, it is perhaps not very useful.

In fact, after reviewing the literature briefly, I wonder if it might be less misleading just to leave that sentence out. (david) 218.214.18.240 14:54, 29 December 2006 (UTC)

Past tense/Present tense

• Introduction: — present tense
• Architecture: — past tense
• Locking: — past tense
• Transaction Processing: — past tense
• Data integrity: — past tense
• Security: — present tense
• Queries: — present tense
• History: — mixed tense
• Future: — present tense

It looks like it would be less work to correct the tense of the 'Introduction', 'Security' and 'Queries' sections. On the other hand, since Jet is still widely used, using the past tense seems a little strange. (david) 218.214.18.240 15:10, 29 December 2006 (UTC)

The article should be entirely in present tense. Jet is still a component of all versions of Windows, as it is used for access to the Active Directory data store (though that's Jet Blue instead of Jet Red). That is why the Windows team maintains the Jet 4.0 version. It is still in use on every single Windows computer since Windows 2000, so there is no reason it should be past tense.

Furthermore, Jet still lives in the forked version that the Access team manages since Access 2007. Yes, they are calling it ACE and Access Database Engine, but it is just a new version of Jet, with enhancements. Dwfenton (talk) 18:15, 15 August 2009 (UTC)

Corruption issues?

The Microsoft Jet Database Engine was used by Diebold in its voting machines that were recently audited in Ohio, and the auditors suspected database corruption. The article below notes that Microsoft says corruption is possible with Jet under similiar circumstances. I think this is important to talk about in this Wikipedia article, so it'd be great if someone more familiar with Jet should find the original source material from MSDN and describe the issue and how it relates to the ongoing issue in Ohio.

http://blog.wired.com/27bstroke6/2007/04/diebold_vote_da.html

Dmazzoni 19:49, 28 April 2007 (UTC)

• Interesting. KB article from MS is http://support.microsoft.com/kb/283849 - Ta bu shi da yu 00:29, 4 May 2007 (UTC)

I don't see why it should be discussed in the article at all. It's a political issue and a mis-use of Jet by a technology company. That's just not relevant here. Dwfenton (talk) 18:17, 15 August 2009 (UTC)

From personal experience, I can say that Jet File Corruption would mainly be attributed to MS-Access Software. When I have used ODBC to connect to MDB files, I have yet to see corruption problems or multi-user concurrency issues. As a result, any new documentation/references/examples added to the article should keep this in mind and not be too critical of Jet Engine. Put the blame where the blame is really due, in other words. Corruption problems can be the result of using the wrong Jet file (MDB) format 2.x, 3.x, 4.x with the MS-Access Driver versions installed. That is important. Also, if the DSN is not configured correctly to handle concurrency, then corruption will happen. See ODBC ADMINISTRATOR utility Help for details. The utility warns that if certain parameters are not set a certain way in the DSN, that unpredictable multi-user concurrency issues could arise. For example: UserCommitSync ODBC Administrator Help says it, "Specifies whether the Microsoft Access driver will perform explicit user-defined transactions asynchronously. The default is "Yes", meaning that the Microsoft Access driver will wait for commits in a user-defined transaction to be completed. Setting this value to "No" can have unpredictable transactional consequences in a multiuser environment." 192.136.195.233 (talk) 22:23, 27 March 2015 (UTC)

Jet version of Access 2003, XP and 2000

Both Access 2003 and XP can save MDB file to a newer format that Access 2000 cannot open. Are they really using the same version of Jet engine?--218.102.91.203 05:59, 31 October 2007 (UTC)

Access developers make a distinction between the database (Jet 4), and the project that is stored in it (Access 2K, 2K2, 2K3). Access 2000 can use the data in a Jet 4 database, (and can 'open' the database enough to get the data in and out), but can't open a 2K2 or 2K3 project even though stored in the same kind of database. 218.214.18.240 (talk) 06:27, 24 November 2007 (UTC)

Implicit transactions

Transactions improved performance in Access 2.0 on Windows 3.11, so implicit transactions were added to Jet 3.0. However, the file system in Windows 95 cached differently, and no longer wrote data to disk under application control. Transactions in Access 2.0 were a way of reducing disk access, but in Jet 3.0 on Windows 95 implicit transactions added overhead without reducing disk access. —Preceding unsigned comment added by 218.214.18.240 (talk) 12:15, 14 July 2008 (UTC)

"Native Jet ISAM Driver"

Where did this term come from? I can't see it anywhere else on the web. The other drivers (the Installable ISAM drivers, IISAM) connected to ISAM database systems, and the term IISAM is still used in the help files, so it sort of makes sense to derive a new term "Native Jet" ISAM driver by analogy, except that Native Jet doesn't use a Sequential Access Method, so calling it an ISAM driver is a bit of a stretch. —Preceding unsigned comment added by 203.206.162.148 (talk) 03:32, 2 March 2009 (UTC)

MDAC Exclusion

The article states:

JET stands for Joint Engine Technology...[and] has since been superseded, ...and no longer exists as a component of Microsoft Data Access Components (MDAC).

The reason Jet is no longer included in the MDAC is not because it is deprecated or obsolete, but because it's unnecessary to include in the MDAC a component that is installed by default in all versions of Windows starting with Windows 2000. I believe this sentence should be changed to reflect that fact. 67.82.153.58 (talk) 16:58, 16 August 2009 (UTC)

Rushmore

Rushmore was technology that MS acquired with the purchase of FoxPro_2 in 1992. It was incorporated into Jet 2, not as the article states in Jet 3. I know this because a significant part of what Rushmore does to enhance performance (in addition to the query optimization) is return the beginning of a dataset before the whole dataset has been retrieved, and this feature was present in Access 2 from the very beginning. Dwfenton (talk) 18:39, 15 August 2009 (UTC)

History: Jet 4.0

There are many problems with this section. The citation points to http://support.microsoft.com/kb/275561/en as the source, and if you read that article you realize there are a number of misrepresentations and outright falsehoods.

- "Jet 4.0 was the last version produced by Microsoft"

Not true. The ACE introduced with Access 2007 is the "last version" of Jet that MS has produced, and is still in active development. Jet 4.0 is the last version that Microsoft calls "Jet" but the Jet database engine is alive and well and under continuing enhancement by the Access development team. Jet 4.0 is a frozen version and is maintained by the Windows development team because it is used for managing the data store of Active Directory.

- "Changes to data types to be more like SQL Server's...Auto-Increment support was dropped..."

Not true. I can't even figure out what part of the cited article this garbled assertion comes from. There seems to be a fundamental misinterpretation of what that article ("Description of the new features that are included in Microsoft Jet 4.0") is about based on apparent ignorance of earlier versions of Jet. Many of the "new features" are not new features of the database engine, but enhancements to allow the use of more widely compatible DDL. In regard to Autoincrement, the article says "The synonym AutoIncrement is no longer supported", which clearly indicates that this is about DDL, not about the features of the underlying engine. Indeed, Jet 4 introduced significant enhancements to its AutoIncrement data type, most specifically, the ability to use DDL and DAO/ADO to set the seed value and increment.

- "Finer grained security"

This appears to be another case of not understanding what the article is about. There is no "finer-grained security," but Jet 4.0's DDL was enhanced to make it possible to manage security via DDL (whereas previously you had to use DAO or the Access UI).

- "views support"

Another case of changed DDL. Jet has always supported the equivalent of VIEWS but calls them SAVED QUERYDEFS. The enhancement is that in DDL you can now use the same commands for creating them that you can use in SQL Server. Where the cited article says "Support for the definition of Views has been added to Microsoft Jet. This capability is built on top of Microsoft Jet's stored query functionality and offers almost exactly the same capability." it's quite clear that this is an enhancement to DDL, not to the underlying database engine.

- "procedure support"

Again, this is misleading. Jet does not have stored procedures. It does have saved DML queries and parameterized queries, but no procedural code is allowed in a Jet QueryDef. Again, the cited article quite clearly states this as "Support for the definition of Procedures has been added to Microsoft Jet. This capability is built on top of Microsoft Jet's stored query functionality of action queries (queries based on UPDATE, DELETE, SELECT INTO, and DROP statements) and SELECT queries that contain parameters." This is an addition to Jet DDL, not a new feature of Jet.

- "Referential integrity support"

Yet again, this makes it sound like RI was added in Jet 4, whereas Jet had engine-level RI from the very first version. What has been added is some new types of RI (e.g., CASCADE NULL) and the appropriate DDL for adding/altering it.

- "A user list, which allows administrators to determine who is connected to the database"

Here again, a misinterpretation. The UserRoster (accessible via ADO) is a feature added to Jet 4, but you could always get this information in the past, but you had to use a non-supported Microsoft-provided DLL, ldbuser.dll. All that has changed is that you can now get this information without needing an additional component.

- "Bi-directional replication with MS SQL Server"

In this case, this is an understatement (and applies only to SQL Server 7 and SQL Server 2000 -- SQL Server 2005 dropped support for heterogeous replication with Jet 4). While heterogenous replication was indeed a new feature of Jet 4, replication itself was significantly enhanced in Jet 4, with the host of features listed in the article. A new bullet point should be added here to summarize the numerous replication changes listed in the cited article. Dwfenton (talk) 18:55, 15 August 2009 (UTC)

Future

The article states:

From a data access technology standpoint, Jet is considered a deprecated technology by Microsoft. The Jet engine is no longer distributed with the latest Microsoft Data Access Components (MDAC).

The last statement is a restatement of the problem cited in the MDAC talk section, and should be amended to reflect that the removal of Jet from the MDAC is not an indication of deprecation, just a consequence of it no longer being needed to be distributed in the MDAC since Jet 4 is a component of all versions of Windows since Windows 2000.

As to "deprecated technology," the cited article (http://msdn.microsoft.com/en-us/library/ms810810.aspx) is a poor support for that. That article is not about Jet, not about Access, but about Microsoft's development platforms in general. Jet *is* deprecated by MS for use with its non-Access development tools, mostly because .NET doesn't live well with a COM-based component. In addition to that, there is the 64-bit issue. In other words, Jet is deprecated as go-to database engine for its development tools (it was a fundamental component of VB versions 3-6), but that doesn't mean it is dead or should not be used in other context. Dwfenton (talk) 19:09, 15 August 2009 (UTC)

JET and Operating System : Jet Red vs Jet Blue

M$: "http://msdn.microsoft.com/en-us/library/windows/desktop/gg269259%28v=exchg.10%29.aspx" yet ... From "http://stackoverflow.com/questions/327199/what-will-we-do-after-access" the link from comment "onedaywhen Jul 6 '09 at 13:00" is deleted from MSDN replaced by "http://msdn.microsoft.com/en-us/library/gg269259%28EXCHG.10%29.aspx"

"ESE was formerly known as Joint Engine Technology (JET) Blue, and so frequently the term "JET Blue" or "JET" is used interchangeably with the term ESE outside this documentation. However, there are in fact two completely separate implementations of the JET API, called JET Blue and JET Red. The term "JET" is frequently also used to refer to JET Red, which is the database engine that is used with Microsoft Office Access. The two JET implementations are completely different, are separately maintained, have a vastly different feature set, and are not interchangeable. Within the ESE documentation, "JET" refers to the ESE or the JET API as ESE implements it. Any references to the JET Red will always explicitly be labeled "JET Red". "

JET engine was used as the SAM (Security Account Manager) database engine when I worked on NT5 codebase (win2000, xp, 2k3 server) (and I believe also earlier NT as well), hence the reference to it being used for Active Directory. The Registry SAM was migrated to SQL based database in NT6 (Vista, Win7, Server 2008 inc. R2), there was documentation of this for Forefront Integration and Identity Manager 2003 available internally but I couldn't find the Technet ref (dead link, moved?). This would be a partial explanation of lack of development of the Jet database for the future. (Experimentation of database based file systems by M$ and IBM (DB2/Domino) seems to have taken a back seat to "big data" like Hadoop.) Shjacks45 (talk) 00:56, 25 February 2012 (UTC)

Jet (Red) has yet to be dis-proven as a viable competitor to SQL Server. Capacity Testing not given in this article. Only opinion to upgrade to SQL Server. That is a NO NO

I find it strange that this article makes a business pitch for SQL Server. Almost like it is trying to fool people into believing they need to upsize to MS SQL server if they want a large database. That should not be allowed in the article as it is not supported by fact, only opinion with revenue motives. I know for a fact that MS-Jet Red databases can contain billions of records. The idea that a Jet database is a single MDB extension file is not supported by any authentic research. Jet Red MDB files are just as likely to represent a single table or a partial table spanning 100's of MDB files. That is the fact of the matter. No OLE linking involved either.

The other thing missing in this article related to my previous comments above is MULTI-USER CONCURENCY CAPACITY TESTING. I it a well known established fact that MS-Jet databases can withstand intensive concurrent ODBC/SQL bombardment. JOINT DIRECT ATTACK MUNITIONS (JDAM) in U.S. Military parlance. Not to be confused with JOINT ENGINE TECHNOLOGY (JET). A Microsoft 2005 White Paper establishes theoretically that 255 concurrent users can connect to a single MDB file and retain good performance, and that some customers (Microsoft acknowledges) may be exceeding these recommended limits. I personally have run 510 concurrent SQL ODBC processes against a single *.MDB Jet 4.x file with reliable SQL report output - over and over again.

Nothing has been said in this article about using MS-Jet (Red) databases in a Distributed/Multi-User environment Such as a single MS-Jet(Red) database containing 1000's of *.MDB files accessed via ODBC enabled applications, and with the database distributed both physically and logically across many (or perhaps dozens) of Windows O/S Servers. The potential for 100s or 1000s of Users hitting this database concurrently and performing Read/Writes has yet to be dis-proven as a viable option to replace upsizing to MS SQL Server. 64.134.147.182 (talk) 19:43, 26 March 2015 (UTC)

Compact/Repair Nothing said about the many option available

Nothing is said in the article about all the options to compact/repair a Jet database file. This can be done through: (1) MS-Access Software Menu Option (2) JetComp.exe standalone utility, in both GUI and command line modes (3) Progammatically using the Method for CompactRepair. (4) 3rd Party Tools (5):

                       ODBC Administrator - You can even create empty Jet 3.x or 4.x database files
                       Then use ODBC/SQL statements from a programming language,
                       to build the tables and indexes for that database file.

64.134.147.182 (talk) 19:43, 26 March 2015 (UTC)