Sustainability of Digital Formats: Planning for Library of Congress Collections |
|
Introduction | Sustainability Factors | Content Categories | Format Descriptions | Contact |
Full name | Microsoft Access MDB File Format Family |
---|---|
Description |
The .mdb extension has been used for a series of proprietary file format versions, developed and used by Microsoft as a native format for its Microsoft Access desktop database management system, which was first released in 1992. Microsoft has provided no public specification for the MDB format, but has supported programming access via the Open Database Connectivity (ODBC) standard and Visual Basic for Applications (VBA). Based on reverse engineering analysis, The unofficial MDB Guide provides an informal description of the MDB format, claiming to cover versions introduced since 1997. Microsoft Access versions from Access 1.0 (1992) through Access 2003, used the .mdb extension for all database files. However, the underlying MDB format changed significantly over the years. With Access 2007, Microsoft introduced a new default file format, using the .accdb extension. See ACCDB_family and, for example, File Formats: ACCDB vs MDB | Access 2007 and Later Use the ACCDB File Format. Despite the advantages of the ACCDB format, there are still reasons to use MDB with Microsoft Access 2016. See, for example, Which Access file format should I use? from Microsoft. MDB file format versions are associated with versions of Microsoft's Jet Database Engine (Jet DB), used as the basis for Access and as part of other products. See Identifying the Jet Database Engine Components for details of the association of MDB versions with Jet DB versions through Access 97. Versions of the MDB format that are usually listed as significantly different correspond to:
More recent versions of Microsoft Access, through Access 2016 (the most recent version as of early 2017), can still create and save databases in the MDB format, providing a compatibility choice between Access 2000 and Access 2002-2003. See Notes below for more detail on releases and discussion on migrating old MDB databases to newer formats and different products. A PRONOM forum thread in March 2013 discussed whether it was necessary to have separate PRONOM entries for Access 95 and 97 and for both Access 2000 and Access 2002-2003. The PRONOM team decided to retain the four separate entries. Developer Considerations When Choosing a File Format in Access 2002 explains compatibility issues between the format introduced in Access 2002 and its 2000 predecessor. According to The Unofficial MDB Guide and Brian Bruns's HACKING file, the structure for the MDB format since Access 97 has the following general structure. The file consists of fixed-size pages (2048 bytes for Jet DB 3; 4096 bytes for Jet DB 4), with the first byte indicating the type of page. The key page types are as follows:
A Microsoft Access database may be split across files. Splitting a database allows file size or table size limitations to be exceeded. For details on limitations for MDB files, see Microsoft Access Specifications and Limitations. Another reason to split a database is for multi-user environments that will benefit from having forms and code in a front-end MDB file on users' desktops, with the data in one or more back-end MDB files on a networked server. An MDB file may also be used as a front end to an ODBC-accessible database server, such as SQL Server or Oracle. |
Production phase | Primarily a middle-state format, typically in active use for recording transactions. |
Relationship to other formats | |
Has subtype | Various chronological versions, not described separately on this website at this time. |
Affinity to | ACCDB_family, Microsoft Access ACCDB File Format Family. Used as default native format for Microsoft Access from Access 2007 on. |
LC experience or existing holdings | Some Library of Congress staff use Microsoft Access as part of their official duties. Many projects using the software began while the default format for Microsoft Access was MDB. The Library of Congress has a small amount of Microsoft Access MDB files in its collections |
---|---|
LC preference | See the Recommended Formats Statement for the Library of Congress format preferences for datasets. |
Disclosure |
Proprietary format developed and managed by Microsoft. |
---|---|
Documentation |
No full specification is publicly available. A description based on reverse engineering can be found at http://jabakobob.net/mdb/. Additional information on the format structure is found in Brian Bruns's HACKING file, which accompanies his mdbtools software utilities. |
Adoption |
Microsoft Access is a widely used desktop database management system for the Windows operating system. According to iDatalabs in early 2017, it ranked third in market share for general-purpose database management systems, behind Microsoft's SQL Server and MySQL. MDB was the default native format from 1992 through 2007. New database applications based on Access are likely to use the ACCDB format but there is a substantial installed base of database applications that use the MDB format. However, no other general-purpose database software uses MDB as a native format and Microsoft has been dropping support for the earlier versions of the MDB format in recent versions of Microsoft Access. There is a flourishing market in products and services that will convert an old MDB file into another format. Extracting the data from an MDB file is relatively straightforward using a current copy of Access that can read the file and a compatible ODBC driver; many conversion tools use this approach. Converting macros, forms, and user interfaces is considerably more complex. As of early 2017, it appears that migrating an Access 1.0 database is considered a major challenge; migrating an Access 2.0 database requires a functioning installation of Access 2000, 2002-2003, or 2007. See Notes below for more detail on migrating or converting old MDB files. Applications for viewing or manipulating data in MDB databases in a Windows environment include: Microsoft Access Runtime, a freely downloadable version of Access with capabilities for end users but not for database design or user customization; MDB Viewer Plus; MDB Admin; and Advanced Query Tool. Some tools and services focus on repair of MDB files, for example: AccessFIX; Stellar Phoenix Access Database Repair; MDB File Recovery Software; and EverythingAccess: Database Repair Service. Applications for working with MDB files exist for various platforms other than Windows. Such utilities include John Li's ACCDB and MDB Database Managers for Android, iOS, and Mac and MDB/ACCDB Viewer for Mac from Egger Apps. Software toolkits for working with MDB files exist for various programming languages and computing environments. For Unix, MDB Tools is a software library in C for programmatic access to MDB files and also provides command-line utilities for useful export or analysis tasks. MDBValidator is a program in python for characterizing an MDB file, including identifying its version. Jackcess is a Java library for reading from and writing to MS Access databases. UCanAccess is an open-source Java JDBC driver supporting reading and writing of Microsoft Access databases. SQLProvider is a toolkit for .NET. For its own computing environments, Microsoft provides Visual Basic for Applications (VBA); ADO.NET; and Data Access Objects DAO. See Data Access API of the Day, a blog post from Microsoft on the history of APIs for its database products. |
Licensing and patents |
The database formats used by Microsoft Access are not covered by Microsoft's Open Specification Promise or Community Promise. Nor does Microsoft use a formal licensing program to cover patents that apply to the MDB format under its Interoperability Program (see Microsoft Interoperability Program (MIP) | Open Specifications Dev Center, Microsoft Interoperability Program). The Language Specification for VBA (Visual Basic for Applications), often used for macros or other code stored within MDB files, is covered by the Community Promise. See also Microsoft Statement on European Commission Decision, December 16, 2009. Although none of Microsoft's official commitments to interoperability cover the MDB format, the compilers of this resource have found no indication that Microsoft has taken any actions to exploit any patents that it might claim cover the file format (as opposed to the Microsoft Access application). [Note: To be active as of early 2017, a patent would have to have been applied for no earlier than 1997 or issued later than 2000.] Comments welcome. |
Transparency |
As a binary format representing a complex data structure, the MDB format can not be fully interpreted using basic tools such as a text editor. For example, all numbers are in binary form. However if the database has neither been encrypted nor compiled into the related MDE format, some textual content, such as table and column names, can be partially viewed in a text editor. |
Self-documentation |
The Microsoft Access application has some capability for descriptive or contextual metadata for the file as a whole using file/database properties, both a default "summary" set and a capability for "custom" properties. A custom property can have a single value of type text, number, date, or binary (Y/N). The Microsoft Access application provides a starter set of ~30 custom properties, including: Date completed, Department, Owner, Purpose. The property names and values can be seen in a Hex dump of the .mdb file. The compilers of this resource are not aware which, if any, of the non-Microsoft tools mentioned above under Adoption, retrieve or reveal these properties. Comments welcome. |
External dependencies | Active use of a database in MDB format usually requires software that can open the particular chronological version of Access. In some circumstances, a compatible ODBC driver is also required. |
Technical protection considerations | MDB files may be password-protected and/or encrypted. If user-level security was applied, the file contains details of the levels and scope of access available to users and groups. None of these security mechanisms are considered secure, individually or in combination. See, for example, What encryption method (algorithm) is used for password protecting MS Access 2003 database? from Stack Overflow and Jet MDB security - under the hood from EverythingAccess.com. |
Dataset | |
---|---|
Normal functionality | MDB files can support a typical set of data types for desktop databases. The list of data types supported appears to have been extended over time. See SQL Data Types from Microsoft Access SQL Reference, for details on data types supported in ODBC. See Microsoft Access Data Types for Microsoft Access data types, data types used to create tables, and ODBC SQL data types, with applicable mappings of terminology and limitations. Comparison of Data Types provides additional information on data type compatibility in different contexts. |
Support for software interfaces (APIs, etc.) |
ODBC is a primary means for querying an MDB database file from other applications or remote copies of Access or for extracting all the table definitions and data from an MDB file for migration to another platform. Use of ODBC to access content in an MDB file requires a functioning version of Microsoft Access that can open the MDB file and a compatible ODBC driver. |
Data documentation (quality, provenance, etc.) | Individual user-defined properties can be applied to the file as a whole. See Self-documentation above. |
Tag | Value | Note |
---|---|---|
Filename extension | mdb |
|
Internet Media Type | See note. | No official registration at IANA. At MIME Types in IIS from 1999, Microsoft lists application/x-msaccess and this appears in many other lists of MIME types, for example, http://www.freeformatter.com/mime-types-list.html. http://filext.com/file-extension/MDB lists less commonly used MIME types. |
Magic numbers | ASCII: ....Standard Jet DB Hex: 00 01 00 00 53 74 61 6E 64 61 72 64 20 4A 65 74 20 44 42 |
This applies to MDB files created with versions of Microsoft Access since Access 95. From Gary Kessler's file signatures page. See also filesignatures.net. |
Indicator for profile, level, version, etc. | See note. | Starting with Access 95, the version of the Jet DB is indicated at offset 0x14 from the beginning of the MDB file. The Hex value of 00 indicates Access 95/97. The Hex value of 01 indicates Access 2002 or Access 2002-2003. See Notes on Microsoft Access Release History below for more information. |
Pronom PUID | x-fmt/66 |
For MDB from Microsoft Access 2.0. See https://www.nationalarchives.gov.uk/PRONOM/x-fmt/66 |
Pronom PUID | x-fmt/238 |
For MDB from Microsoft Access 95. See https://www.nationalarchives.gov.uk/PRONOM/x-fmt/238 |
Pronom PUID | x-fmt/239 |
For MDB from Microsoft Access 97. See https://www.nationalarchives.gov.uk/PRONOM/x-fmt/239 |
Pronom PUID | x-fmt/240 |
For MDB from Microsoft Access 2000. See https://www.nationalarchives.gov.uk/PRONOM/x-fmt/240 |
Pronom PUID | x-fmt/241 |
For MDB from Microsoft Access 2002. See https://www.nationalarchives.gov.uk/PRONOM/x-fmt/241 |
Wikidata Title ID | Q1931564 |
No version distinctions. See https://www.wikidata.org/wiki/Q1931564. |
General |
Related file extensions: In addition to the MDB format, Microsoft Access provides an alternate format for distributing a database application, using the .mde extension. See Convert MDB to MDE File Format, which states, "The MDE file format is a special extension of the standard Microsoft Access MDB format, and is used to distribute an application. An MDE file compiles all of the modules, removes all editable source code and compacts the database. The resulting MDE file allows the database users to perform normal database operations, however it prohibits any changes from being made to the objects that support, or the code that runs the application." Another associated extension is .mdn, used for an Access Blank Database Template. For a complete list of extensions associated with Microsoft Access, see Wikipedia entry for Microsoft Access. Migration of old MDB database files to later versions of Microsoft Access: The definitions and contents of tables in a Microsoft Access database can typically be successfully migrated from one version of MDB to a later one, assuming the copy of Access is able to open the source database. Macros, forms, etc., may require modification to function properly in the newer environment. A selection of articles or posts offering advice or indicating possible issues follow: Review of Access 2.0 -- InfoWorld Aug 15, 1994 recommended use of the Convert Database function but provided the caveat that some "database objects" would probably need modification. Looking to convert Access 1.0 MDB databases to later version? (from 2010) is a useful thread relating to converting a very old version of MDB. It was imported successfully into Access 2007. Microsoft's Import an Access 95 database into an Access 2007 file states, "To import forms and reports that contain VBA code (and also modules), first convert your Access 95 database to the Access 2002 - 2003 or Access 2000 file format by using Microsoft Office Access 2003, Access 2002, or Access 2000." Running old versions of Access may require running old versions of Windows. Developer Considerations When Choosing a File Format in Access 2002 discusses incompatibilities between Access 2000 and Access 2002. How to convert Access Databases (.mdb) prior to upgrading to Access 2013 offers guidance for conversion from MDB to ACCDB. Meanwhile, Backward compatibility between Access 2010 and Access 2007 provides guidance related to saving files as MDB in newer versions of Access. The conclusion of the compilers of this resource is that migration to a new version of Microsoft Access is not necessarily straightforward if the intention is to retain and preserve the full functionality of the database application. However, it may prove convenient as a step in a preservation process if the intent is simply to capture the database structure and data content. Comments welcome. Copying data from an MDB database file to another format: No other software uses the MDB format as its native database format. However, the underlying relational database model and ODBC, which supports remote use of SQL for creating and querying databases, provide a level of interoperability that can be exploited effectively in many situations, including migration of data in an MDB file to another format. ODBC can be used to extract all the table definitions and data from an MDB file for migration to another platform. It is important to note that use of ODBC requires a functioning version of Microsoft Access that can open the MDB file and a compatible ODBC driver. This is not as simple as it sounds; it might require using an old operating system or installing different versions of ODBC drivers. ODBC is the basis for many conversion tools or procedures in other database systems. See, for example, MySQL Workbench Manual: Microsoft Access Migration. This is also the mechanism used by SIARD (Software Independent Archiving of Relational Databases) for making archival copies of databases, including from Microsoft Access databases, in the SIARD format. Software that claims to support export to other formats include: MDB Admin (application for Windows); MDB/ACCDB Viewer (application for Mac); and MDB Tools (software library in C for Unix). Related Microsoft utility: Microsoft Access Runtime is a freely downloadable version of Access that with capabilities for end users but not for database design or user customization. This viewer can be used for queries and to run pre-defined reports on MDB files supported by the associated version of Access. This means that the 2016 version can be used to look at MDB files back though those created by Access 2000. It is primarily designed for distribution with applications developed based on Microsoft Access. Relationship to ESRI Personal Geodatabase format: The format used by ESRI for its Personal Geodatabase is based on MDB. However, in Understanding how to use Microsoft Access files in ArcGIS, ESRI warns against editing a Personal Geodatabase in Access, because the database may be corrupted. ESRI has been discouraging the use of Personal Geodatabases in favor of the File Geodatabase. See, for example, ArcMap Help | Types of geodatabases and Personal Geodatabases are not supported in 64-bit versions of ArcGIS. A sample Personal Geodatabase file encountered indicates that it is compatible with Access 2000 or Access 2002-2003 (Jet DB 2). For information on ESRI Geodatabases in general, see ESRI Arc Geodatabase . |
---|---|
History |
Microsoft Access Release History: Microsoft Access 1.0 was released in November 1992. After a minor release (1.1) in May 1993, Access 2.0 came out in 1994; it was advertised for sale by August 1994. Starting with Office 95, new versions of Access were released with new versions of the Office Professional Suite and supported the VBA language for scripting. The releases with MDB as default format were : Access 95 (aka Access 7.0); Access 97 (version 8.0); Access 2000 (version 9.0); Access 2002 (version 10.0); and Access 2003 (version 11.5). Access 2007 (version 12.0) introduced the ACCDB format as the default database format and used a new ACE database engine. Some new features added in Access 2010 prevent the database from being saved in the MDB file format. Because MDB files can still be used with later versions of Access, continued release history may be relevant. Major releases were Access 2010 (version 14.0); Access 2013 (version 15.0); Access 2016 (version 16.0). Office 2016 was released in September 2015. Some plans for Office 365 subscribers, which uses frequent releases, include Access. Between September 2015 and March 2017, only one of the changes listed in What's new in Access 2016 would have had any effect on the associated file format. Using the Large Number data type indicates that this feature, adding support for storing larger integers, prevents use of versions of Access prior to Access 2016. From a technical perspective, the format version for a database with the .mdb extension is identified by the version of the Jet Database Engine (Jet DB) with which it is compatible. For versions of Access prior to Access 95, the version of the Jet DB corresponded to the version of Access. See INFO: Identifying the Jet Database Engine Components for details. Access 95/97 used versions 3.x of the Jet DB with a page size of 2048 bytes. Access 2000/2003 used versions 4.x of the Jet DB with a page size of 4096 bytes. Information on later versions can be found in The Unofficial MDB Guide: First Page and the MDBtools HACKING page. Since Access 95 (at least), the Jet DB version can be found in the file header of an MDB file, at offset 0x14:
Versions of Microsoft Access since 2007 can still write databases in the MDB format. Access 2010 through Access 2016 offer a choice of two MDB formats: Access 2000 and Access 2002-2003. The compilers of this resource used Access 2010 to create a minimal database in the Access 2000 format. This MDB file had the same magic number and version indicator as indicated above. PRONOM treats these as a single byte sequence in Microsoft Access Database 2000 | Signatures. PRONOM provides a second byte sequence as a signature that indicates the version/build of Microsoft Access used to create the file. However, the MDB database created using Access 2010 did not include this second byte sequence. For this reason, the File Signifiers table above does not include the second byte sequence. Comments welcome. For the other Office applications, Microsoft has been careful to retain backwards compatibility and application functionality to open old files. This is no longer the case for Access database files. Support for the Access 1.0 and Access 2.0 formats was dropped with Access 2010. Support for Access 95 and Access 97 formats was dropped with Access 2013. Early history of ODBC support in Microsoft Access: ODBC (Open Database Connectivity) is a standard application programming interface (API) for accessing database management systems (DBMS). By the late 1980s, it was clear that SQL (Structured Query Language) was an important standard for databases. Collaborative efforts involving several vendors, including Microsoft, developed a specification using the name SQL Connectivity (SQLC), published in 1989. While some vendors focused on official standardization of the SQL Call Level Interface that was part of the SQLC proposal, Microsoft worked on the SQLC proposal for an API, and published it separately in December 1991. After industry input and a change of name, ODBC 1.0 was released in September 1992. Microsoft Access 1.0 came out soon after. InfoWorld, August 16, 1993, page 68 reviewed Access 1.1 and indicated that it introduced ODBC support for access to Oracle and Sybase together with improved ODBC performance for accessing data on Microsoft's own SQL Server. This suggests that Access 1.0 had some support for ODBC, likely limited to its use with SQL Server. Page 17 of the same issue of InfoWorld reported that Microsoft was shipping its first stand-alone ODBC drivers. InfoWorld, Jan 25, 1993, page 49 had mentioned Microsoft's intention to be able to share information between Access and SQL Server and InfoWorld, Mar 7, 1994, page 23 indicated that the upcoming release 2.2 of ClearReports would have ODBC drivers for using Access as a data source. This suggests that, at least by Access 1.1, it was possible to access data in an MDB file from other applications via ODBC. |
|