Access Database Engine

Last updated

Access Database Engine
Other names
  • ACE Database Engine
  • Office Access Connectivity Engine (ACE)
  • Microsoft JET Engine
  • Microsoft Jet Database Engine
  • Jet Red
  • Joint Engine Technology (JET)
Developer(s) Microsoft
Initial release1992;32 years ago (1992)
Stable release
ACE 16
Operating system Microsoft Windows
Type Database engine
Website office.microsoft.com/en-us/access/   OOjs UI icon edit-ltr-progressive.svg

The Access Database Engine (also Office Access Connectivity Engine or ACE and formerly Microsoft Jet Database Engine, Microsoft JET Engine or simply Jet) is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

Contents

JET stands for Joint Engine Technology. Microsoft Access and Visual Basic use or have used Jet as their underlying database engine. However, it has been superseded for general use, first by Microsoft Desktop Engine (MSDE), then later by SQL Server Express. For larger database needs, Jet databases can be upgraded (or, in Microsoft parlance, "up-sized") to Microsoft's flagship SQL Server database product.

A five billion record MS Jet (Red) database with compression and encryption turned on requires about one terabyte of disk storage space[ citation needed ]. It comprises typically hundreds of *.mdb files.

Architecture

Jet, being part of a relational database management system (RDBMS), allows the manipulation of relational databases. It offers a single interface that other software can use to access Microsoft databases and provides support for security, referential integrity, transaction processing, indexing, record and page locking, and data replication. In later versions, the engine has been extended to run SQL queries, store character data in Unicode format, create database views and allow bi-directional replication with Microsoft SQL Server.

Jet DLLs Jet DLLs.svg
Jet DLLs

There are three modules to Jet: One is the Native Jet ISAM Driver, a dynamic link library (DLL) that can directly manipulate Microsoft Access database files (MDB) using a (random access) file system API. Another one of the modules contains the ISAM Drivers, DLLs that allow access to a variety of Indexed Sequential Access Method ISAM databases, among them xBase, Paradox, Btrieve and FoxPro, depending on the version of Jet. The final module is the Data Access Objects (DAO) DLL. DAO provides an API that allows programmers to access JET databases using any programming language.

Locking

Jet allows multiple users to access the database concurrently. To prevent that data from being corrupted or invalidated when multiple users try to edit the same record or page of the database, Jet employs a locking policy. Any single user can modify only those database records (that is, items in the database) to which the user has applied a lock, which gives exclusive access to the record until the lock is released. In Jet versions before version 4, a page locking model is used, and in Jet 4, a record locking model is employed. Microsoft databases are organized into data "pages", which are fixed-length (2  kB before Jet 4, 4 kB in Jet 4) data structures. Data is stored in "records" of variable length that may take up less or more than one page. The page locking model works by locking the pages, instead of individual records, which though less resource-intensive also means that when a user locks one record, all other records on the same page are collaterally locked. As a result, no other user can access the collaterally locked records, even though no user is accessing them and there is no need for them to be locked. In Jet 4, the record locking model eliminates collateral locks, so that every record that is not in use is available.

There are two mechanisms that Microsoft uses for locking: pessimistic locking, and optimistic locking. With pessimistic locking, the record or page is locked immediately when the lock is requested, while with optimistic locking, the locking is delayed until the edited record is saved. Conflicts are less likely to occur with optimistic locking, since the record is locked only for a short period of time. However, with optimistic locking one cannot be certain that the update will succeed because another user could lock the record first. With pessimistic locking, the update is guaranteed to succeed once the lock is obtained. Other users must wait until the lock is released in order to make their changes. Lock conflicts, which either require the user to wait, or cause the request to fail (usually after a timeout) are more common with pessimistic locking.

Transaction processing

Jet supports transaction processing for database systems that have this capability (ODBC systems have one-level transaction processing, while several ISAM systems like Paradox do not support transaction processing). A transaction is a series of operations performed on a database that must be done together this is known as atomicity and is one of the ACID (Atomicity, Consistency, Isolation, and Durability), concepts considered to be the key transaction processing features of a database management system. For transaction processing to work (until Jet 3.0), the programmer needed to begin the transaction manually, perform the operations needed to be performed in the transaction, and then commit (save) the transaction. Until the transaction is committed, changes are made only in memory and not actually written to disk. Transactions have a number of advantages over independent database updates. One of the main advantages is that transactions can be abandoned if a problem occurs during the transaction. This is called rolling back the transaction, or just rollback, and it restores the state of the database records to precisely the state before the transaction began. Transactions also permit the state of the database to remain consistent if a system failure occurs in the middle of a sequence of updates required to be atomic. There is no chance that only some of the updates will end up written to the database; either all will succeed, or the changes will be discarded when the database system restarts. With ODBC's in-memory policy, transactions also allow for many updates to a record to occur entirely within memory, with only one expensive disk write at the end.

Implicit transactions were supported in Jet 3.0. These are transactions that are started automatically after the last transaction was committed to the database. Implicit transactions in Jet occurred when an SQL DML statement was issued. However, it was found that this had a negative performance impact in 32-bit Windows (Windows 95, Windows 98), so in Jet 3.5 Microsoft removed implicit transactions when SQL DML statements were made.

Data integrity

Jet enforces entity integrity and referential integrity. Jet will by default prevent any change to a record that breaks referential integrity, but Jet databases can instead use propagation constraints (cascading updates and cascading deletes) to maintain referential integrity.

Jet also supports "business rules" (also known as "constraints"), or rules that apply to any column to enforce what data might be placed into the table or column. For example, a rule might be applied that does not allow a date to be entered into a date_logged column that is earlier than the current date and time, or a rule might be applied that forces people to enter a positive value into a numeric only field.

Security

Access to Jet databases is done on a per user-level. The user information is kept in a separate system database, and access is controlled on each object in the system (for instance by table or by query). In Jet 4, Microsoft implemented functionality that allows database administrators to set security via the SQL commands CREATE, ADD, ALTER, DROP USER and DROP GROUP. These commands are a subset of ANSI SQL 92 standard, and they also apply to the GRANT/REVOKE commands. [1] When Jet 2 was released, security could also be set programmatically through DAO.

Queries

Queries are the mechanisms that Jet uses to retrieve data from the database. They can be defined in Microsoft QBE (Query By Example), through the Microsoft Access SQL Window or through Access Basic's Data Access Objects (DAO) language. These are then converted to a SQL SELECT statement. The query is then compiled this involves parsing the query (involves syntax checking and determining the columns to query in the database table), then converting into an internal Jet query object format, which is then tokenized and organized into a tree-like structure. In Jet 3.0 onward these are then optimized using the Microsoft Rushmore query optimization technology. The query is then executed and the results passed back to the application or user who requested the data.

Jet passes the data retrieved for the query in a dynaset. This is a set of data that is linked dynamically back to the database. Instead of having the query result stored in a temporary table, where the data cannot be updated directly by the user, the dynaset allows the user to view and update the data contained in the dynaset. Thus, if a university lecturer queries all students who received a distinction in their assignment and finds an error in that student's record, the user would only need to update the data in the dynaset, which would automatically update the student's database record without the need for the user to send a specific update query after storing the query results in a temporary table.

History

Jet versionJet engineDLL file nameSupported database versions
1.0 ? ?1.0
1.11.10.0001MSAJT110.DLL
  • 1.0
  • 1.1
2.02.00.0000MSAJT200.DLL
  • 1.0
  • 1.1
  • 2.0
2.52.50.1606MSAJT200.DLL
  • 1.0
  • 1.1
  • 2.0
3.03.0.0.2118MSJT3032.DLL
  • 1.0
  • 1.1
  • 2.0
  • 3.0
3.53.51.3328.0MSJET35.DLL
  • 1.0
  • 1.1
  • 2.0
  • 3.X
4.0 SP84.0.8015.0MSJET40.DLL
  • 1.0
  • 1.1
  • 2.0
  • 3.X
  • 4.0
ACE 1212.0.xxxx.xxxxACECORE.DLL
  • 1.0
  • 1.1
  • 2.0
  • 3.X
  • 4.0
  • ACE
ACE 1414.0.xxxx.xxxxACECORE.DLL
  • 3.X
  • 4.0
  • ACE
ACE 1515.0.xxxx.xxxxACECORE.DLL
  • 4.0
  • ACE
ACE 1616.0.xxxx.xxxxACECORE.DLL
  • 4.0
  • ACE
Application/VersionJet version
Microsoft Access 1.01.0
Microsoft Access 1.11.1
Microsoft Access 2.02.0
Microsoft Access 2.0 Service Pack2.5
  • Microsoft Access 95
  • Excel 95
3.0
3.5
Microsoft Access 20004.0 SP1
Microsoft Access 2002 [2]
Microsoft Access 2003 [3]
Microsoft Access 2007ACE 12
Microsoft Access 2010ACE 14
Microsoft Access 2013ACE 15
Microsoft Access 2016ACE 16
Visual Basic 3.01.1
Visual Basic Compatibility Layer2.0
Visual Basic 4.0 16-bit2.5
Visual Basic 4.0 32-bit3.0
Visual Basic 5.03.5
Visual C++ 4.X3.0
Visual C++ 5.03.5
3.0
Internet Information Server 3.03.5
SQL Server 7.04.0
Redistributable installers
Jet 3.51 web download3.5+
MDAC 2.14.0 SP1
MDAC 2.54.0 SP3 to SP6+
Jet 4.04.0 SP3 to SP8
2007 Office System DriverACE 12
Microsoft Access Database Engine 2010ACE 14
Microsoft Access Database Engine 2013 ACE 15
Microsoft Access Database Engine 2016 ACE 16
Operating systems
Windows Me 4.0 SP3
Windows 2000 4.0 SP3
Windows XP 4.0 SP5+
Windows Server 2003 4.0 SP6+
Windows Vista 4.0 SP8+
Windows Server 2008 4.0 SP8+
Windows 7 4.0 SP8+

Jet originally started in 1992 as an underlying data access technology that came from a Microsoft internal database product development project, code-named Cirrus. Cirrus was developed from a pre-release version of Visual Basic code and was used as the database engine of Microsoft Access. Tony Goodhew, who worked for Microsoft at the time, says

"It would be reasonably accurate to say that up until that stage Jet was more the name of the team that was assigned to work on the DB engine modules of Access rather than a component team. For VB [Visual Basic] 3.0 they basically had to tear it out of Access and graft it onto VB. That's why they've had all those Jet/ODBC problems in VB 3.0."

Jet became more componentized when Access 2.0 was released because the Access ODBC developers used parts of the Jet code to produce the ODBC driver. A retrofit was provided that allowed Visual Basic 3.0 users to use the updated Jet issued in Access 2.0. [4]

Jet 2.0 was released as several dynamic linked libraries (DLL's) that were utilized by application software, such as Microsoft's Access database. DLL's in Windows are "libraries" of common code that can be used by more than one applicationby keeping code that more than one application uses under a common library which each of these applications can use independently code maintenance is reduced and the functionality of applications increases, with less development effort. Jet 2.0 comprised three DLL's: the Jet DLL, the Data Access Objects (DAO) DLL and several external ISAM DLL's. The Jet DLL determined what sort of database it was accessing, and how to perform what was requested of it. If the data source was an MDB file (a Microsoft Access format) then it would directly read and write the data to the file. If the data source was external, then it would call on the correct ODBC driver to perform its request. The DAO DLL was a component that programmers could use to interface with the Jet engine, and was mainly used by Visual Basic and Access Basic programmers. The ISAM DLL's were a set of modules that allowed Jet to access three ISAM based databases: xBase, Paradox and Btrieve. Jet 2.0 was replaced with Jet 2.1, which used the same database structure but different locking strategies, making it incompatible with Jet 2.0.

Jet 3.0 included many enhancements, including a new index structure that reduced storage size and the time that was taken to create indices that were highly duplicated, the removal of read locks on index pages, a new mechanism for page reuse, a new compacting method for which compacting the database resulted in the indices being stored in a clustered-index format, a new page allocation mechanism to improve Jet's read-ahead capabilities, improved delete operations that sped up processing, multi-threading (three threads were used to perform read ahead, write behind, and cache maintenance), implicit transactions (users did not have to instruct the engine to start manually and commit transactions to the database), a new sort engine, long values (such as memos or binary data types) were stored in separate tables, and dynamic buffering (whereby Jet's cache was dynamically allocated at start up and had no limit and which changed from a first in, first out (FIFO) buffer replacement policy to a least recently used (LRU) buffer replacement policy). [5] Jet 3.0 also allowed for database replication. Jet 3.0 was replaced by Jet 3.5, which uses the same database structure, but different locking strategies, making it incompatible with Jet 3.0.

Jet 4.0 gained numerous additional features and enhancements. [1]

Microsoft Access versions from Access 2000 to Access 2010 included an "Upsizing Wizard" which could "upsize" (upgrade) a Jet database to "an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original database". Reports, queries, macros and security were not handled by this tool, meaning that some manual modifications might have been needed if the application was heavily reliant on these Jet features. [6]

A standalone version of the Jet 4 database engine was a component of Microsoft Data Access Components (MDAC), and was included in every version of Windows from Windows 2000 on. [7] The Jet database engine was only 32-bit and did not run natively under 64-bit versions of Windows. This meant that native 64-bit applications (such as the 64-bit versions of SQL Server) could not access data stored in MDB files through ODBC, OLE DB, or any other means, except through intermediate 32-bit software (running in WoW64) that acted as a proxy for the 64-bit client. [8]

With version 2007 onward, Access includes an Office-specific version of Jet, initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine (However MS-Access consultants and VBA developers who specialize in MS-Access are more likely to refer to it as "the ACE Database Engine").[ citation needed ] This engine was backward-compatible with previous versions of the Jet engine, so it could read and write (.mdb) files from earlier Access versions. It introduced a new default file format, (.accdb), that brought several improvements to Access, including complex data types such as multi-value fields, the attachment data type and history tracking in memo fields. It also brought security changes and encryption improvements and enabled integration with Microsoft Windows SharePoint Services 3.0 and Microsoft Office Outlook 2007. [9] [10] [11] It can be obtained separately. [12]

The engine in Microsoft Access 2010 discontinued support for Access 1.0, Access 2.0, Lotus 1-2-3 and Paradox files. [13] A 64-bit version of Access 2010 and its ACE Driver/Provider was introduced, which in essence provides a 64-bit version of Jet. The driver is not part of the Windows operating system, but is available as a redistributable. [14] [15]

The engine in Microsoft Access 2013 discontinued support for Access 95, Access 97 and xBase files, and it also discontinued support for replication. [16]

Version 1608 of Microsoft Access 2016 restored support for xBase files, [17] and Version 1703 introduced a Large Number data type. [18]

From a data access technology standpoint, Jet is considered a deprecated technology by Microsoft, [19] but Microsoft continues to support ACE as part of Microsoft Access.

Compatibility

Microsoft provides the JET drivers for Microsoft Windows only and third party software support for JET databases is almost exclusively found on Windows. However, there are open source projects that enable working with JET databases on other platforms including Linux. Notably, MDB Tools and its much extended Java port named Jackcess as well as UCanAccess.

See also

Related Research Articles

<span class="mw-page-title-main">Microsoft Access</span> Database manager part of the Microsoft 365 package

Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Access Database Engine (ACE) with a graphical user interface and software-development tools. It is a member of the Microsoft 365 suite of applications, included in the Professional and higher editions or sold separately.

In computing, Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems. An application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

A database transaction symbolizes a unit of work, performed within a database management system against a database, that is treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in a database. Transactions in a database environment have two main purposes:

  1. To provide reliable units of work that allow correct recovery from failures and keep a database consistent even in cases of system failure. For example: when execution prematurely and unexpectedly stops in which case many operations upon a database remain uncompleted, with unclear status.
  2. To provide isolation between programs accessing a database concurrently. If this isolation is not provided, the programs' outcomes are possibly erroneous.
<span class="mw-page-title-main">Firebird (database server)</span> Relational database management system

Firebird is an open-source SQL relational database management system that supports Linux, Microsoft Windows, macOS and other Unix platforms. The database forked from Borland's open source edition of InterBase in 2000 but the code has been largely rewritten since Firebird 1.5.

SAP ASE (Adaptive Server Enterprise), originally known as Sybase SQL Server, and also commonly known as Sybase DB or Sybase ASE, is a relational model database server developed by Sybase Corporation, which later became part of SAP SE. ASE was developed for the Unix operating system, and is also available for Microsoft Windows.

Btrieve is a transactional database software product. It is based on Indexed Sequential Access Method (ISAM), which is a way of storing data for fast retrieval. There have been several versions of the product for DOS, Linux, older versions of Microsoft Windows, 32-bit IBM OS/2 and for Novell NetWare.

Btrieve is a database developed by Pervasive Software. The architecture of Btrieve has been designed with record management in mind. This means that Btrieve only deals with the underlying record creation, data retrieval, record updating and data deletion primitives. Together with the MicroKernel Database Engine it uses ISAM, Indexed Sequential Access Method, as its underlying storage mechanism.

The following tables compare general and technical information for a number of relational database management systems. Please see the individual products' articles for further information. Unless otherwise specified in footnotes, comparisons are based on the stable versions without any add-ons, extensions or external programs.

Microsoft SQL Server Data Engine is a relational database management system developed by Microsoft. It is a scaled-down version of Microsoft SQL Server 7.0 or 2000 which is free for non-commercial use as well as certain limited commercial use. It was introduced at Microsoft TechEd in May 1999, and was included as part of Microsoft Office 2000 Developer Edition. Its successor, SQL Server Express was released in November 2005. Vendor support of MSDE ended on April 8, 2008.

Extensible Storage Engine (ESE), also known as JET Blue, is an ISAM data storage technology from Microsoft. ESE is the core of Microsoft Exchange Server, Active Directory, and Windows Search. It is also used by a number of Windows components including Windows Update client and Help and Support Center. Its purpose is to allow applications to store and retrieve data via indexed and sequential access.

Jet Data Access Objects is a general programming interface for database access on Microsoft Windows systems, primarily for Jet and ACE databases.

<span class="mw-page-title-main">Microsoft Data Access Components</span> Framework

Microsoft Data Access Components is a framework of interrelated Microsoft technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). There have been several deprecated components as well, such as the Jet Database Engine, MSDASQL, and Remote Data Services (RDS). Some components have also become obsolete, such as the former Data Access Objects API and Remote Data Objects.

Borland Database Engine (BDE) is the Windows-based core database engine and connectivity software behind Borland Delphi, C++Builder, IntraBuilder, Paradox for Windows, and Visual dBASE for Windows.

MyISAM was the default storage engine for the MySQL relational database management system versions prior to 5.5 released in December 2009. It is based on the older ISAM code, but it has many useful extensions.

Microsoft SQL Server Compact is a discontinued relational database produced by Microsoft for applications that run on mobile devices and desktops. Prior to the introduction of the desktop platform, it was known as SQL Server for Windows CE and SQL Server Mobile Edition.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

An embedded database system is a database management system (DBMS) which is tightly integrated with an application software; it is embedded in the application. It is a broad technology category that includes:

<span class="mw-page-title-main">Actian Zen</span>

Actian Zen is an ACID-compliant, zero-DBA, embedded, nano-footprint, multi-model, Multi-Platform database management system (DBMS) developed originally by Pervasive Software, which was acquired by Actian Corporation in 2013.

The following outline is provided as an overview of and topical guide to MySQL:

The history of Microsoft SQL Server begins with the first Microsoft SQL Server database product – SQL Server v1.0, a 16-bit relational database for the OS/2 operating system, released in 1989.

References

  1. 1 2 MS KB article 275561 (January 29, 2007). "Description of the new features that are included in Microsoft Jet 4.0". Microsoft. Retrieved June 19, 2008.{{cite web}}: CS1 maint: numeric names: authors list (link)
  2. The Access 2002 setup program only updated system files on certain versions of Windows and to a certain level.
  3. Access 2003 relied on the Jet engine component of the operating system for its data storage and query processing.
  4. Goodhew, Tony (November 1996). "Jet Engine: History". Archived from the original on August 8, 2017. Retrieved March 28, 2020.
  5. MS KB article 137039 (December 3, 2003). "New Features in Microsoft Jet Version 3.0". Microsoft. Retrieved June 19, 2008.{{cite web}}: CS1 maint: numeric names: authors list (link)
  6. Microsoft, "Microsoft Access 2000 Data Engine Options", white paper.
  7. MS KB article 239114 (May 29, 2008). "How to obtain the latest service pack for the Microsoft Jet 4.0 Database Engine". Microsoft. Retrieved January 2, 2010.{{cite web}}: CS1 maint: numeric names: authors list (link)
  8. Gorm Braarvig. "Access database from SQL 2005/64" . Retrieved June 18, 2007.
  9. Jakšić, Aleksandar (August 2008). "Developing Access 2007 Solutions with Native C or C++". Microsoft Corporation. Retrieved August 26, 2008.
  10. Andy Baron, Optimizing Microsoft Office Access Applications Linked to SQL Server, November 2006.
  11. Microsoft, New features of the Access 2007 file format Archived 2009-12-27 at the Wayback Machine .
  12. 2007 Office System Driver: Data Connectivity Components
  13. Microsoft, Discontinued features and modified functionality in Access 2010.
  14. Adam W. Saxton, Microsoft SQL Server Escalation Services (January 21, 2010). "How to get a x64 version of Jet?" . Retrieved October 29, 2021.
  15. "Microsoft Access Database Engine 2010 Redistributable". Archived from the original on September 7, 2010.
  16. Microsoft, Discontinued features and modified functionality in Access 2013.
  17. Microsoft, Back by popular demand—dBASE file support in Access
  18. Microsoft, What's New in Access 2016
  19. Shirolkar, Prash; Henry, Alyssa; Pepitone, Stephen; Bunch, Acey J. (January 2008). "Data Access Technologies Road Map". Microsoft Corporation. Retrieved June 19, 2008.

Further reading