The Microsoft Jet Database Engine is the database engine where several Microsoft products have been created. The database engine is the underlying component of the database, the collection of information stored on the computer in a systematic way. The first Jet version was developed in 1992, consisting of three modules that can be used to manipulate the database.
JET stands for Joint Engine Technology , sometimes referred to as Microsoft JET Engine or just Jet . Microsoft Access and the use of Visual Basic or have used Jet as the underlying database engine. Since then it has been replaced for general use, however, first by Microsoft Desktop Engine (MSDE), then by SQL Server Express. For larger database needs, Jet database can be upgraded (or, in Microsoft language, "size-up") to Microsoft's flagship database product, SQL Server.
However, this does not mean that the MS Jet (Red) database can not match MS SQL Server in storage capacity. A 5 billion records of MS Jet (Red) databases with compression and encryption turned on requires about 1 terabyte of disk storage space, consisting of hundreds (*.mdb) files, each acting as a partial table, and not as a database itself.
Over the years, Jet is almost identical to Microsoft Access, to the point that many people refer to the Jet database as "Access database".
Video Microsoft Jet Database Engine
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, recording and locking pages, and data replication. In newer versions, the machine has been extended to run SQL queries, storing character data in Unicode format, creating database views, and enabling bidirectional replication with Microsoft SQL Server.
There are three modules for Jet: One is Native Jet ISAM Driver , a dynamic link library (DLL) that can directly manipulate Microsoft Access (MDB) database files using Indexed Sequential Access Method (ISAM). Another module contains ISAM Drivers , DLLs that allow access to various ISAM databases, including xBase, Paradox, Btrieve and FoxPro, depending on the Jet version. The last module is Data Access Objects (DAO) DLL. DAO provides an API that allows programmers to access a JET database using any programming language.
Lock
Jet allows many users to access the database simultaneously. To prevent data from being damaged or invalid when multiple users try to edit the same record or page from the database, Jet uses a lock policy. Each single user can modify only those database records (ie, items in the database) to which the user has implemented a key, which gives exclusive access to the records until the key is released. In the Jet version before version 4, the page locking model is used, and in Jet 4, the record locking model is used. Microsoft database is set to "page" data, which is a fixed-length data structure (2 kB before Jet 4, 4 kB in Jet 4). Data is stored in "records" of variable lengths that can take fewer or more than one page. The page locking model works by locking a page instead of an individual record, which although less resource intensive also means that when a user locks a record, all other records on the same page are collectively locked. As a result, no other users can access records that are locked collectively, even if no user accesses them and does not need to be locked. In Jet 4, the recording lock model eliminates the collateral key, so any unused records are available.
There are two mechanisms that Microsoft uses to lock: pessimistic locking , and optimistic locking . With pessimistic locking, a note or page is locked immediately when the key is requested, while with optimistic locking, the lock is suspended until the edited record is saved. Conflict tends not to occur with optimistic locking, since the record is locked for only a short time. However, with optimistic locking, it is not certain that the update will work because other users can lock records first. With pessimistic locking, updates are guaranteed to be successful once keys are obtained. Other users must wait until the key is released to make changes. Key conflicts, which require the user to wait, or cause the request to fail (usually after the deadline) are more common with pessimistic locking.
Transaction processing
Jet supports transaction processing for database systems that have this capability. (The ODBC system has transaction processing on one level, while some ISAM systems like Paradox do not support transaction processing.) Transactions are a series of operations performed on databases to be done together - this is known as atomicity and is one of ACID (Atomicity, Consistency , Isolation, and Durability), a concept that is considered to be a major transaction processing feature of a database management system. In order for the transaction process to work (up to Jet 3.0), the programmer needs to start the transaction manually, perform the operations that need to be done in the transaction, and then commit the transaction. Until the transaction is done, the changes are only done in memory and not actually written to the disk. [1] The transaction has a number of advantages over an independent database update. One of the main advantages is that a transaction may be left out if a problem occurs during a transaction. This is called returning a transaction, or simply restoring, and returning the status of the database record to the exact state before the transaction begins. Transactions also allow the state of the database to remain consistent if a system failure occurs in the middle of the renewal sequence required to become atomic. There is no chance that only some updates will eventually be written to the database; everything will work, or changes will be discarded when the database system restarts. With the in-memory ODBC policy, the transaction also allows many updates to records to occur entirely in memory, with only one expensive disk writing at the end.
Implicit transactions are supported in Jet 3.0. This is a transaction that starts automatically after the last transaction is made to the database. An implicit transaction in Jet occurs when a SQL DML statement is issued. However, it was found that this had a negative performance impact on 32-bit Windows (Windows 95, Windows 98), so in Jet 3.5 Microsoft removed an implicit transaction when a SQL DML statement was created.
Data integrity
Jets impose entity integrity and referential integrity. Jet by default will prevent any changes to recordings that damage referential integrity, but the Jet database can use propagation limits (stream updates and cascading deletes) to maintain referential integrity.
Jet also supports "business rules" (also known as "restrictions"), or rules that apply to any column to enforce what data may be placed into tables or columns. For example, a rule may be applied that does not allow dates to be entered in date_logged columns earlier than the current date and time, or rules may be applied that force people to enter a positive value into a numeric field only.
Security
Access to the Jet database is done at a per-user level. User information is stored in separate system databases, and access is controlled on every object in the system (for example by table or on request). In Jet 4, Microsoft implements functions that allow database administrators to manage security through SQL commands CREATE, ADD, ALTER, DROP USER and DROP GROUP. These commands are part of the standard ANSI SQL 92, and also apply to the GRANT/REVOKE command. When Jet 2 is released, security can also be set programmatically via DAO.
Queries
The question is the mechanism that Jet uses to retrieve data from the database. They can be defined in Microsoft QBE (Query By Example), through Microsoft Access SQL Window or through Access Access Data Access (DAO) language. This is then converted into a SQL SELECT statement. This query is then compiled - this involves query parsing (involving syntax checking and specifying columns for queries in the database table), then converting to an internal Jet query object format, which is then validated and organized into tree-like structures. In Jet 3.0 and beyond, this is then optimized using Microsoft Rushmore query optimization technology. The query is then executed and the results are passed back to the application or user requesting the data.
Jet forwards the data retrieved for queries in a dynaset. This is a set of data that is dynamically linked back to the database. Instead of having query results stored in a temporary table, where data can not be updated directly by the user, dynaset allows users to view and update the data contained in the dynaset. So if a university lecturer asks all the students who accept the differences in their assignment and finds errors in the student's notes, they just need to update the data in the dynaset, which will automatically update the student database records without requiring them to post specific update requests after storing query results in the temporary table.
Maps Microsoft Jet Database Engine
History
Jet originally started in 1992 as an underlying data access technology originating from Microsoft's internal database product development project, code named Cirrus. Cirrus developed from a pre-release version of Visual Basic code and used as a Microsoft Access database engine. Tony Goodhew, who worked for Microsoft at the time, said
"That would be pretty accurate to say that up until that stage the more Jet team names were assigned to work on the Access DB engine modules than the component teams.For VB [Visual Basic] 3.0 they basically had to tear it from Access and transplant it into VB.That's why they has all the Jet/ODBC issues in VB 3.0. "
Jets become more componentised when Access 2.0 is released because the Access ODBC developer uses part of the Jet code to generate an ODBC driver. Retrofit is provided that allows Visual Basic 3.0 users to use the updated Jet issued in Access 2.0.
Jet 2.0 is released as some dynamic linked libraries (DLLs) used by application software, such as Microsoft Access databases. DLLs in Windows are common code "libraries" that can be used by more than one application - by storing code that is used by more than one application under common libraries that each of these applications can use independent code maintenance is reduced and the functionality of the app increases, with little development effort. Jet 2.0 consists of three DLLs: Jet DLLs, Data Access Objects (DAO) DLLs and some external ISAM DLLs. The DLL Jet determines what kind of database it accesses, and how to do what is required of it. If the data source is an MDB file (Microsoft Access format) it will directly read and write data to a file. If it is an external data source, it will call the correct ODBC driver to perform the request. DAO DLL is a component that programmers can use to interact with Jet engines, and is primarily used by Visual Basic and Access Basic programmers. The ISAM DLLs are a set of modules that allow Jet to access three ISAM-based databases: xBase, Paradox and Btrieve. Jet 3.0 includes many improvements, including a new index structure that reduces storage size and time taken to create highly duplicated indexes, deletion of lock keys on index pages, new mechanisms for page reuse, new compacting methods for compressing databases generate indexes that stored in a clustered index format, new page allocation mechanisms to improve Jet read-ahead capability, improve deletion operations that speed up processing, multithreading (three threads used for reading, write write back, and cache maintenance), implicit transactions (users do not need to instruct machine to manually start and make transactions to the database), new sorting machines, long values ​​(such as memos or binary data types) are stored in separate tables, and dynamic buffers (where the Jet cache is dynamically allocated at start up and has no limits and that changed from the wisdom n first buffer replacement, first exit (FIFO) to Replacement buffer policy (LRU) at least recently used). Jet 3.0 also allows database replication. Jet 3.0 is 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 has many additional features and enhancements.
Microsoft Access versions of Access 2000 to Access 2010 include the "Upsizing Wizard" which can "upgrade" (increase) the Jet database to "an equivalent database on SQL Server with the same table structure, data, and many other attributes of the original database". Reports, questions, macros and security are not handled by this tool, which means that some manual modifications may be required if the application is heavily dependent on this Jet feature.
A standalone version of the Jet 4 database engine is a component of Microsoft Data Access Components (MDAC), and is included in every Windows version of Windows 2000 on. The Jet database engine is only 32-bit and does not run natively under Windows 64-bit versions. This means that native 64-bit applications (such as 64-bit versions of SQL Server) can not access data stored in an MDB file via ODBC, OLE DB, or other means, except through medium 32-bit software (running on WoW64) which acts as a proxy for 64-bit clients.
With the 2007 version and so on, Access includes an Office-specific Jet version, originally called the Office Access Connectivity Engine (ACE), but is now called Access Database Engine (However, Access and VBA developers who specialize in MS-Access are more likely to call it "ACE Database Engine"). This machine is compatible with earlier versions of Jet engine, so it can read and write (.mdb) files from previous Access versions. This introduces a new default file format, (.accdb), which brings some improvements to Access, including complex data types such as multinate fields, attachment data types and history tracking in the memo field. It also brings security changes and fixes encryption and enables integration with Microsoft Windows SharePoint Services 3.0 and Microsoft Office Outlook 2007.
The machines in Microsoft Access 2010 stop support for Access 1.0, Access 2.0, Lotus 1-2-3, and Paradox files. The 64-bit version of Access 2010 and its ACE Driver/Provider was introduced, which essentially provides a 64-bit version of Jet. Drivers are not part of the Windows operating system, but are available as redistributable.
The machines in Microsoft Access 2013 stop support for Access 95, Access 97 and xBase files, and also stop support for replication.
Version 1608 of Microsoft Access 2016 restores support for xBase files, and Version 1703 introduces Large Number data types.
From a data access technology standpoint, Jet is considered a technology left by Microsoft, but Microsoft continues to support ACE as part of Microsoft Access.
Compatibility
Microsoft provides JET drivers only for Microsoft Windows. Therefore, third-party software support for JET databases is almost exclusively found in Windows. There are open source projects that try to enable working with JET databases on other platforms, MDB Tools and many extended Java ports named Jackcess.
References
Further reading
- The Microsoft Database Network Engine Programmer's Guide, Microsoft, 1995
- Library of Congress, Microsoft Accesses Family MDB File Formats
- Library Congress, Microsoft Access ACCDB File Format Family
Source of the article : Wikipedia
0 Comments