Microsoft Jet Database Engine
Encyclopedia
The Microsoft Jet Database Engine is a database engine
on which several Microsoft
products have been built. A database engine is the underlying component of a database
, a collection of information stored on a computer in a systematic way. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.
JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access
and Visual Basic
use or have used Jet as their underlying database engine. It has since been superseded for general use, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server Express
. Jet is now part of Microsoft Windows and is no longer a component of Microsoft Data Access Components
(MDAC). For larger database needs, Jet databases can be upgraded (or, in Microsoft parlance, "up-sized") to Microsoft's flagship database product, SQL Server.
Over the years, Jet has become almost synonymous with Microsoft Access, to the extent where many people refer to a Jet database as an "Access database". Even Microsoft employees do this sometimes. There are parties that see this as incorrect and state that Jet is a database and Access is a database application development tool (database builder) .
and is part of a Relational Database Management System
(RDBMS). It offers a single interface
that other software can use to access Microsoft databases and provides support for security, referential integrity
, transaction
processing
, index
ing, record
and page locking, and data replication. In later versions, the engine has been extended to be able to run SQL
queries, store character data in Unicode
format, create database views
and allow bi-directional replication with Microsoft SQL Server.
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 Indexed Sequential Access Method (ISAM). Another one of the modules contains the ISAM Drivers, DLLs that allow access to a variety of 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.
that gives them exclusive access to the record until the lock is released. Up to Jet 4, a page locking model was 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 that divide up the database. Data is stored in "records", but these are of variable length and so may take up less or more than one page. The page locking model worked by locking the pages, instead of individual records, which though less resource intensive also meant that more than one record might be locked at any one time.
There were 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 update is delayed until all the editing operations on the record have been completed. 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 update the record first. With pessimistic locking, the update is guaranteed to succeed once the lock is obtained. Other users must wait until the update is made and the lock 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.
for database systems that have this capability (ODBC
systems have one level transaction processing, while several ISAM systems like Paradox do not have transaction processing capability). A transaction is a series of operations performed on a database that must be done together — this is known as atomicity and is a part of 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.
Jet enforces entity integrity
and referential integrity
. Entity integrity is one of the key concepts of relational database
s, and ensures that no record is able to be duplicated and also ensures that no field (or group of fields) that identify the record (the primary key) are NULL
. Thus, Jet supports primary keys. Referential integrity is where the fields that identify data that exist in a database table (the foreign key
) must correspond with an existing primary key in that database. If a foreign key value exists that does not have a corresponding primary key in the referenced table, then the referential integrity is broken and the data between tables will no longer be synchronised.
For instance, a music lover may have a database that stores information about a record collection, and need to store data about an artist and his/her music. In this example, the artist can record many albums, but the album is only recorded by one artist, so two database tables are created: Artist and Album. The Artist table uses the field artist_id as its primary key, and the Album table uses album_id. The album table references the artist table using artist_id as a foreign key. If, for some reason, an artist is deleted and there is an album in the system that contains a reference to that artist then the referential integrity of this record would be broken. Jet will by default prevent this from happening. Jet is also capable of doing cascading updates and deletes. With cascading deletes enabled for the Album table, if the artist in the previous example were deleted, then all the artists' albums would also be deleted.
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.
.
statement. The query is then compile
d — this involves parsing the query (involves syntax checking and determining the columns to query in the database table), then converted into an internal Jet query object format, which is then tokenized and organised into a tree like structure. In Jet 3.0 onwards these are then optimised using the Microsoft Rushmore query optimisation 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 dynamically linked 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, they would only need to update the data in the dynaset, which would automatically update the student's database record without the need for them to send a specific update query after storing the query results in a temporary table.
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
Jet became more componentised 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.
Jet 2.0 was released as several dynamic linked libraries (DLLs) that were utilised by application software
, such as Microsoft's Access database. DLLs in Windows
are "libraries" of common code that can be used by more than one application—by 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. The three DLLs that were comprised by Jet 2.0 were the Jet DLL, the Data Access Objects
(DAO) DLL and several external ISAM DLLs. 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 DLLs 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 speeded processing, multithreading
(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). 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.
Jet 4 databases can be "upsized
" (upgraded) to "an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original database" if the developer has a copy of Microsoft Office
2000 Professional Edition via a Microsoft Access Upsizing Wizard utility. Reports, queries, macros and security are not handled by this tool, meaning that some manual modifications may need to be done if the developer has been heavily reliant on these Jet features.
Jet has been included in every version of Windows from Windows 2000 to Windows 7, and therefore is no longer distributed separately with the Microsoft Data Access Components
(MDAC). Access 2003 relied on the Jet engine component of the operating system for its data storage and query processing.
With version 2007 onwards, 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. This engine is fully backward-compatible with previous versions of the Jet engine, so it reads and writes (.mdb) files from earlier Access versions. It introduces a new default file format, (.accdb), that brings several improvements to Access, including complex data types such as multivalue fields, the attachment data type and history tracking in memo fields. It also brings security and encryption improvements and enables integration with Microsoft Windows SharePoint Services 3.0 and Microsoft Office Outlook 2007.
The engine in Microsoft Access 2010 discontinued support for Access 1.0, Access 2.0, Lotus 1-2-3 and Paradox files. A 64-bit
version of the ACE Driver/Provider has been 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. Previously 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.
From a data access technology standpoint, Jet is considered a deprecated technology by Microsoft, but it is an intrinsic part of Windows, and therefore Microsoft continues to support it.
There is an Open Source project that attempts to enable working with JET databases on other platforms, the MDB Tools
.
Database engine
A database engine is the underlying software component that a database management system uses to create, read, update and delete data from a database....
on which several Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
products have been built. A database engine is the underlying component of a database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
, a collection of information stored on a computer in a systematic way. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.
JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access
Microsoft Access
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
and Visual Basic
Visual Basic
Visual Basic is the third-generation event-driven programming language and integrated development environment from Microsoft for its COM programming model...
use or have used Jet as their underlying database engine. It has since been superseded for general use, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server Express
SQL Server Express
Microsoft SQL Server Express, a freely downloadable and distributable version of Microsoft's SQL Server relational database management system, comprises a database specifically targeted for embedded and smaller-scale applications.- Capabilities :...
. Jet is now part of Microsoft Windows and is no longer a component of Microsoft Data Access Components
Microsoft Data Access Components
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 , OLE DB, and Open Database Connectivity...
(MDAC). For larger database needs, Jet databases can be upgraded (or, in Microsoft parlance, "up-sized") to Microsoft's flagship database product, SQL Server.
Over the years, Jet has become almost synonymous with Microsoft Access, to the extent where many people refer to a Jet database as an "Access database". Even Microsoft employees do this sometimes. There are parties that see this as incorrect and state that Jet is a database and Access is a database application development tool (database builder) .
Architecture
Jet allows the manipulation of a relational databaseRelational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
and is part of a Relational Database Management System
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(RDBMS). It offers a single interface
Interface (computer science)
In the field of computer science, an interface is a tool and concept that refers to a point of interaction between components, and is applicable at the level of both hardware and software...
that other software can use to access Microsoft databases and provides support for security, referential integrity
Referential integrity
Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
, transaction
Database transaction
A transaction comprises a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions...
processing
Transaction processing
In computer science, transaction processing is information processing that is divided into individual, indivisible operations, called transactions. Each transaction must succeed or fail as a complete unit; it cannot remain in an intermediate state...
, index
Index (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
ing, record
Record locking
Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.The classic example is demonstrated by two bank clerks attempting to update the same bank account for two different transactions. Clerks 1 and 2 both retrieve the account's...
and page locking, and data replication. In later versions, the engine has been extended to be able to run SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
queries, store character data in Unicode
Unicode
Unicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems...
format, create database views
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
and allow bi-directional replication with Microsoft SQL Server.
XBase
xBase is the generic term for all programming languages that derive from the original dBASE programming language and database formats. These are sometimes informally known as dBASE "clones"...
, Paradox
Paradox (database)
Paradox is a relational database management system currently published by Corel Corporation. It was originally released for DOS by Ansa Software, and then by Borland after it bought the company...
, Btrieve
Btrieve
Btrieve is a transactional database software product. It is based on Indexed Sequential Access Method , which is a way of storing data for fast retrieval...
and FoxPro
FoxPro
' has two meanings:*Visual FoxPro, an object-oriented programming language and RDBMS, published by Microsoft, for Microsoft Windows*FoxPro 2, a text-based procedural programming language and DBMS, originally published by Fox Software and later by Microsoft, for MS-DOS, Microsoft Windows, Macintosh,...
, depending on the version of Jet. The final module is the Data Access Objects (DAO) DLL. DAO
Data Access Objects
Jet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
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 write to the database, Jet employs a data write locking policy. Any single user can only modify those database records (that is, items in the database) to which they have applied a lockLock (computer science)
In computer science, a lock is a synchronization mechanism for enforcing limits on access to a resource in an environment where there are many threads of execution. Locks are one way of enforcing concurrency control policies.-Types:...
that gives them exclusive access to the record until the lock is released. Up to Jet 4, a page locking model was used, and in Jet 4 a record locking model is employed. Microsoft databases are organized into data "pages", which are fixed length (2 kB
Kilobyte
The kilobyte is a multiple of the unit byte for digital information. Although the prefix kilo- means 1000, the term kilobyte and symbol KB have historically been used to refer to either 1024 bytes or 1000 bytes, dependent upon context, in the fields of computer science and information...
before Jet 4, 4 kB in Jet 4) data structures that divide up the database. Data is stored in "records", but these are of variable length and so may take up less or more than one page. The page locking model worked by locking the pages, instead of individual records, which though less resource intensive also meant that more than one record might be locked at any one time.
There were two mechanisms that Microsoft uses for locking
Concurrency control
In information technology and computer science, especially in the fields of computer programming , operating systems , multiprocessors, and databases, concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible.Computer...
: 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 update is delayed until all the editing operations on the record have been completed. 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 update the record first. With pessimistic locking, the update is guaranteed to succeed once the lock is obtained. Other users must wait until the update is made and the lock 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 processingTransaction processing
In computer science, transaction processing is information processing that is divided into individual, indivisible operations, called transactions. Each transaction must succeed or fail as a complete unit; it cannot remain in an intermediate state...
for database systems that have this capability (ODBC
Open Database Connectivity
In computing, ODBC is a standard C interface for accessing database management systems . The designers of ODBC aimed to make it independent of database systems and operating systems...
systems have one level transaction processing, while several ISAM systems like Paradox do not have transaction processing capability). A transaction is a series of operations performed on a database that must be done together — this is known as atomicity and is a part of ACID
ACID
In computer science, ACID is a set of properties that guarantee database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction...
(Atomicity, Consistency, Isolation, and Durability), concepts considered to be the key transaction processing features of a database management system
Database management system
A database management system is a software package with computer programs that control the creation, maintenance, and use of a database. It allows organizations to conveniently develop databases for various applications by database administrators and other specialists. A database is an integrated...
. 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
SQL
SQL is a programming language designed for managing data in relational database management systems ....
DML
Data Manipulation Language
A data manipulation language is a family of syntax elements similar to a computer programming language used for inserting, deleting and updating data in a database...
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
Entity integrity
In the relational data model, entity integrity is one of the three inherent integrity rules. Entity integrity is an integrity rule which states that every table must have a primary key and that the column or columns chosen to be the primary key should be unique and not null.A direct consequence of...
and referential integrity
Referential integrity
Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
. Entity integrity is one of the key concepts of relational database
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s, and ensures that no record is able to be duplicated and also ensures that no field (or group of fields) that identify the record (the primary key) are NULL
Null
-In computing:* Null , a special marker and keyword in SQL* Null character, the zero-valued ASCII character, also designated by NUL, often used as a terminator, separator or filler* Null device, a special computer file that discards all data written to it...
. Thus, Jet supports primary keys. Referential integrity is where the fields that identify data that exist in a database table (the foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
) must correspond with an existing primary key in that database. If a foreign key value exists that does not have a corresponding primary key in the referenced table, then the referential integrity is broken and the data between tables will no longer be synchronised.
For instance, a music lover may have a database that stores information about a record collection, and need to store data about an artist and his/her music. In this example, the artist can record many albums, but the album is only recorded by one artist, so two database tables are created: Artist and Album. The Artist table uses the field artist_id as its primary key, and the Album table uses album_id. The album table references the artist table using artist_id as a foreign key. If, for some reason, an artist is deleted and there is an album in the system that contains a reference to that artist then the referential integrity of this record would be broken. Jet will by default prevent this from happening. Jet is also capable of doing cascading updates and deletes. With cascading deletes enabled for the Album table, if the artist in the previous example were deleted, then all the artists' albums would also be deleted.
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
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
. 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. When Jet 2 was released, security could also be set programmatically through DAOData Access Objects
Jet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
.
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 an SQL SELECTSelect
Select or SELECT may refer to:* Select , an album by Kim Wilde* Select , a British music magazine* Select , a keyword in SQL* select , a system call for polling multiple file descriptors...
statement. The query is then compile
Compile
Compile may refer to:* Compile , a Japanese video game company founded in 1983 that specialized in shoot 'em up and computer puzzle game genres...
d — this involves parsing the query (involves syntax checking and determining the columns to query in the database table), then converted into an internal Jet query object format, which is then tokenized and organised into a tree like structure. In Jet 3.0 onwards these are then optimised using the Microsoft Rushmore query optimisation 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
Dynaset
1. A dynaset is a set of data that is dynamically linked 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...
. This is a set of data that is dynamically linked 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, they would only need to update the data in the dynaset, which would automatically update the student's database record without the need for them to send a specific update query after storing the query results in a temporary table.
History
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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
Microsoft Access
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools. It is a member of the Microsoft Office suite of...
. 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 componentised 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.
Jet 2.0 was released as several dynamic linked libraries (DLLs) that were utilised by application software
Application software
Application software, also known as an application or an "app", is computer software designed to help the user to perform specific tasks. Examples include enterprise software, accounting software, office suites, graphics software and media players. Many application programs deal principally with...
, such as Microsoft's Access database. DLLs in Windows
Microsoft Windows
Microsoft Windows is a series of operating systems produced by Microsoft.Microsoft introduced an operating environment named Windows on November 20, 1985 as an add-on to MS-DOS in response to the growing interest in graphical user interfaces . Microsoft Windows came to dominate the world's personal...
are "libraries" of common code that can be used by more than one application—by 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. The three DLLs that were comprised by Jet 2.0 were the Jet DLL, the Data Access Objects
Data Access Objects
Jet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
(DAO) DLL and several external ISAM DLLs. 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 DLLs were a set of modules that allowed Jet to access three ISAM based databases: Xbase
XBase
xBase is the generic term for all programming languages that derive from the original dBASE programming language and database formats. These are sometimes informally known as dBASE "clones"...
, Paradox
Paradox (database)
Paradox is a relational database management system currently published by Corel Corporation. It was originally released for DOS by Ansa Software, and then by Borland after it bought the company...
and Btrieve
Btrieve
Btrieve is a transactional database software product. It is based on Indexed Sequential Access Method , which is a way of storing data for fast retrieval...
.. 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
Index (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
structure that reduced storage size and the time that was taken to create indices that were highly duplicated, the removal of read locks
Lock (database)
A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to write to the database. Any single user can only modify those database records to which they have applied a lock that gives them exclusive...
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 speeded processing, multithreading
Thread (computer science)
In computer science, a thread of execution is the smallest unit of processing that can be scheduled by an operating system. The implementation of threads and processes differs from one operating system to another, but in most cases, a thread is contained inside a process...
(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). 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.
- UnicodeUnicodeUnicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems...
character storage support, along with an NTWindows NTWindows NT is a family of operating systems produced by Microsoft, the first version of which was released in July 1993. It was a powerful high-level-language-based, processor-independent, multiprocessing, multiuser operating system with features comparable to Unix. It was intended to complement...
sorting method that was also implemented in the Windows 95Windows 95Windows 95 is a consumer-oriented graphical user interface-based operating system. It was released on August 24, 1995 by Microsoft, and was a significant progression from the company's previous Windows products...
version; - Changes to data types to be more like SQL ServerMicrosoft SQL ServerMicrosoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
'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
- SQLSQLSQL is a programming language designed for managing data in relational database management systems ....
enhancements to make Jet conform more closely to ANSIAnsiAnsi is a village in Kaarma Parish, Saare County, on the island of Saaremaa, Estonia....
SQL-92SQL-92SQL-92 was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. For all but a few minor incompatibilities, the SQL-89 standard is forwards-compatible with SQL-92.... - Finer grained security; views support; procedure support
- Invocation and termination (committing or rolling back) of transactions
- Enhanced table creation and modification
- Referential integrityReferential integrityReferential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
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.
Jet 4 databases can be "upsized
Upsizing (database)
Upsizing is the term coined by Microsoft to describe the process of upgrading Microsoft Access Database to a Microsoft SQL Server. This allows to continue using Microsoft Access as a database front-end whereas the actual back-end is served by a separate local or remote SQL Server allowing much...
" (upgraded) to "an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original database" if the developer has a copy of Microsoft Office
Microsoft Office
Microsoft Office is a non-free commercial office suite of inter-related desktop applications, servers and services for the Microsoft Windows and Mac OS X operating systems, introduced by Microsoft in August 1, 1989. Initially a marketing term for a bundled set of applications, the first version of...
2000 Professional Edition via a Microsoft Access Upsizing Wizard utility. Reports, queries, macros and security are not handled by this tool, meaning that some manual modifications may need to be done if the developer has been heavily reliant on these Jet features.
Jet has been included in every version of Windows from Windows 2000 to Windows 7, and therefore is no longer distributed separately with the Microsoft Data Access Components
Microsoft Data Access Components
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 , OLE DB, and Open Database Connectivity...
(MDAC). Access 2003 relied on the Jet engine component of the operating system for its data storage and query processing.
With version 2007 onwards, 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. This engine is fully backward-compatible with previous versions of the Jet engine, so it reads and writes (.mdb) files from earlier Access versions. It introduces a new default file format, (.accdb), that brings several improvements to Access, including complex data types such as multivalue fields, the attachment data type and history tracking in memo fields. It also brings security and encryption improvements and enables integration with Microsoft Windows SharePoint Services 3.0 and Microsoft Office Outlook 2007.
The engine in Microsoft Access 2010 discontinued support for Access 1.0, Access 2.0, Lotus 1-2-3 and Paradox files. A 64-bit
64-bit
64-bit is a word size that defines certain classes of computer architecture, buses, memory and CPUs, and by extension the software that runs on them. 64-bit CPUs have existed in supercomputers since the 1970s and in RISC-based workstations and servers since the early 1990s...
version of the ACE Driver/Provider has been 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. Previously the Jet Database Engine was only 32-bit
32-bit
The range of integer values that can be stored in 32 bits is 0 through 4,294,967,295. Hence, a processor with 32-bit memory addresses can directly access 4 GB of byte-addressable memory....
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
OLE DB
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
, or any other means, except through intermediate 32-bit software (running in WoW64
WoW64
WoW64 is a subsystem of the Windows operating system that is capable of running 32-bit applications and is included on all 64-bit versions of Windows—including Windows XP Professional x64 Edition, IA-64 and x64 versions of Windows Server 2003, as well as 64-bit versions of Windows Vista, Windows...
) that acted as a proxy for the 64-bit client.
From a data access technology standpoint, Jet is considered a deprecated technology by Microsoft, but it is an intrinsic part of Windows, and therefore Microsoft continues to support it.
Compatibility
Microsoft provides the JET drivers only for Microsoft Windows. Therefore, third party software support for JET databases is almost exclusively found on Windows.There is an Open Source project that attempts to enable working with JET databases on other platforms, the MDB Tools
MDB Tools
The MDB tools project is an open source effort to create a set of software libraries and utilities to manipulate files in the proprietary JET 3 and 4 database formats...
.