Access Database Engine
Other names |
|
---|---|
Developer(s) | Microsoft |
Initial release | 1992 |
Stable release | ACE 16
|
Operating system | Microsoft Windows |
Type | Database engine |
Website | office |
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
JET stands for Joint Engine Technology.
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
There are three modules to Jet: One is the Native Jet ISAM Driver, a
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
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
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
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.
Queries
Queries are the mechanisms that Jet uses to retrieve data from the database. They can be defined in
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 version | Jet engine | DLL file name | Supported database versions |
---|---|---|---|
1.0 | ? | ? | 1.0 |
1.1 | 1.10.0001 | MSAJT110.DLL |
|
2.0 | 2.00.0000 | MSAJT200.DLL |
|
2.5 | 2.50.1606 | MSAJT200.DLL |
|
3.0 | 3.0.0.2118 | MSJT3032.DLL |
|
3.5 | 3.51.3328.0 | MSJET35.DLL |
|
4.0 SP8 | 4.0.8015.0 | MSJET40.DLL |
|
ACE 12 | 12.0.xxxx.xxxx | ACECORE.DLL |
|
ACE 14 | 14.0.xxxx.xxxx | ACECORE.DLL |
|
ACE 15 | 15.0.xxxx.xxxx | ACECORE.DLL |
|
ACE 16 | 16.0.xxxx.xxxx | ACECORE.DLL |
|
Sources:
|
Application/Version | Jet version |
---|---|
Microsoft Access 1.0 | 1.0 |
Microsoft Access 1.1 | 1.1 |
Microsoft Access 2.0 | 2.0 |
Microsoft Access 2.0 Service Pack | 2.5 |
|
3.0 |
|
3.5 |
Microsoft Access 2000 | 4.0 SP1 |
Microsoft Access 2002 | [2] |
Microsoft Access 2003 | [3] |
Microsoft Access 2007 | ACE 12 |
Microsoft Access 2010 | ACE 14 |
Microsoft Access 2013 | ACE 15 |
Microsoft Access 2016 | ACE 16 |
Visual Basic 3.0 | 1.1 |
Visual Basic Compatibility Layer | 2.0 |
Visual Basic 4.0 16-bit | 2.5 |
Visual Basic 4.0 32-bit | 3.0 |
Visual Basic 5.0 | 3.5 |
Visual C++ 4.X
|
3.0 |
Visual C++ 5.0 | 3.5 |
|
3.0 |
Internet Information Server 3.0 | 3.5 |
SQL Server 7.0 | 4.0 |
Redistributable installers | |
Jet 3.51 web download | 3.5+ |
MDAC 2.1 | 4.0 SP1 |
MDAC 2.5 | 4.0 SP3 to SP6+ |
Jet 4.0 | 4.0 SP3 to SP8 |
2007 Office System Driver | ACE 12 |
Microsoft Access Database Engine 2010 | ACE 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+ |
Sources:
|
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
Jet 3.0 included many enhancements, including a new
Jet 4.0 gained numerous additional features and enhancements.[1]
- Unicode character storage support, along with an NT sorting method that was also implemented in the Windows 95 version;
- Changes to data types to be more like SQL Server's (LongText or Memo; Binary; LongBinary; Date/Time; Real; Float4; IEEESingle; Double; Byte or Tinyint; Integer or Integer synonyms Smallint, Integer2, and Short; LongInteger or LongInteger synonyms Int, Integer, and Counter; Currency or Money; Boolean and GUID); a new decimal data type
- Memo fields could now be indexed
- Compressible data types
- ANSI SQL-92
- Finer grained security; views support; procedure support
- Invocation and termination (committing or rolling back) of transactions
- Enhanced table creation and modification
- Referential integrity support
- Connection control (connected users remain connected, but once disconnected they cannot reconnect, and new connections cannot be made. This is useful for database administrators to gain control of the database)
- A user list, which allows administrators to determine who is connected to the database
- Record-level locking (previous versions only supported page-locking)
- Bi-directional replication with MS SQL Server.
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
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").[
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
References
- ^ a b 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) - ^ The Access 2002 setup program only updated system files on certain versions of Windows and to a certain level.
- ^ Access 2003 relied on the Jet engine component of the operating system for its data storage and query processing.
- ^ Goodhew, Tony (November 1996). "Jet Engine: History". Archived from the original on August 8, 2017. Retrieved March 28, 2020.
- ^ 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) - ^ Microsoft, "Microsoft Access 2000 Data Engine Options", white paper.
- ^ 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) - ^ Gorm Braarvig. "Access database from SQL 2005/64". Retrieved June 18, 2007.
- ^ Jakšić, Aleksandar (August 2008). "Developing Access 2007 Solutions with Native C or C++". Microsoft Corporation. Retrieved August 26, 2008.
- ^ Andy Baron, Optimizing Microsoft Office Access Applications Linked to SQL Server, November 2006.
- ^ Microsoft, New features of the Access 2007 file format Archived 2009-12-27 at the Wayback Machine.
- ^ 2007 Office System Driver: Data Connectivity Components
- ^ Microsoft, Discontinued features and modified functionality in Access 2010.
- ^ Adam W. Saxton, Microsoft SQL Server Escalation Services (January 21, 2010). "How to get a x64 version of Jet?". Retrieved October 29, 2021.
- ^ "Microsoft Access Database Engine 2010 Redistributable". Archived from the original on September 7, 2010.
- ^ Microsoft, Discontinued features and modified functionality in Access 2013.
- ^ Microsoft, Back by popular demand—dBASE file support in Access
- ^ Microsoft, What's New in Access 2016
- ^ Shirolkar, Prash; Henry, Alyssa; Pepitone, Stephen; Bunch, Acey J. (January 2008). "Data Access Technologies Road Map". Microsoft Corporation. Retrieved June 19, 2008.
Further reading
- Microsoft Jet Database Engine Programmer's Guide, Microsoft, 1995
- Library of Congress, Microsoft Access MDB File Format Family
- Library of Congress, Microsoft Access ACCDB File Format Family