Sustainability of Digital Formats: Planning for Library of Congress Collections

Introduction | Sustainability Factors | Content Categories | Format Descriptions | Contact
Format Description Categories >> Browse Alphabetical List

Microsoft Access ACCDB File Format Family

>> Back
Table of Contents
Format Description Properties Explanation of format description terms

Identification and description Explanation of format description terms

Full name Microsoft Access ACCDB File Format Family
Description

The .accdb file format extension has been used for a proprietary file format, developed by Microsoft and used as the default format for Microsoft Access versions from Access 2007 through Access 2016 (current version as of May 2017). See, for example, Introduction to the Access 2007 file format from Microsoft and File Formats: ACCDB vs MDB | Access 2007 and Later Use the ACCDB File Format. The .mdb file format extension was used for all databases created by Access prior to Access 2007. See Microsoft Access MDB Format Family. 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. Microsoft has provided no public specification for the ACCDB format, but supports programming access via the Open Database Connectivity (ODBC) standard and Visual Basic for Applications (VBA). Other than the Microsoft Access application, ODBC is the primary means of access to data in an Access database from other software, including other database applications.

Although there was a reverse engineering effort to document the predecessor MDB format publicly, there appears to be no equivalent activity for the ACCDB format. A key difference between ACCDB and MDB is that with Access 2007, Microsoft introduced a new database engine (the Office Access Connectivity Engine, referred to also as ACE and Microsoft Access Database Engine) to replace the Jet database used for MDB files. See Wikipedia entry for Microsoft Jet Database Engine. Versions of the ACE engine released through May 2017, some of which can be downloaded at no charge from Microsoft, include ACE 12 (Access 2007); ACE 10 (Access 2010); ACE 15 (Access 2013); ACE 16 (Access 2016). Microsoft refers to the ACCDB format in the singular, but use of some features introduced into later versions of the Access application will result in database files that cannot be opened by earlier versions. See, for example: Backward compatibility between Access 2010 and Access 2007; and What's new in Access 2016. For this reason, the compilers of this resource consider that ACCDB must have subtypes. A detailed analysis has not been attempted at this time, given the lack of public documentation; this description covers the family of ACCDB formats. Comments welcome.

A hex dump of a simple ACCDB file suggests that there are general similarities in structure to the latest versions of the predecessor MDB Format Family. Both file formats use fixed page sizes of 4096 bytes; this is confirmed by Wayne Phillips at Changing the Encryption Type in Access 2007 on EverythingAccess.com. Another similarity between ACCDB and MDB is the form of the magic number, which includes the string "Standard ACE DB" for ACCDB. A version or compatibility code is in the same location in both formats. The mdbtools | HACKING file states "Offset 0x14 contains the Jet version of this database" and The unofficial MDB Guide concurs. The information in these sources, combined with Wikipedia entry for Microsoft Jet Database Engine, suggests that a value of 0x02 indicates ACE 12 (Access 2007) and 0x03 indicates ACE 14 (Access 2010). However, a minimal database created in Access 2010 and a similar one created in Access 2016 both have 0x02 in this location. A minimal database created in Access 2016, but defining a column with the newly introduced "large integer" data type, had a value of 0x05. In ACCDB files, this indicator appears to reflect compatibility of the file rather than the version of the ACE engine used to create it.

Since the introduction of Access 2016, Microsoft has been strongly encouraging use of subscription-based purchase of the Office applications and the cloud-based Office 365 service. These applications are updated frequently and updates can introduce new features whose use means that a database cannot be used with a previous version of the application. Of the changes listed in What's New in Access 2016 as of May 2017, a new feature introduced in March 2017 would have this effect if used. Using the Large Number data type indicates that this feature, adding support for storing larger integers, prevents use of versions of Access prior to the current Access 2016.

A Microsoft Access database may be split across files. Splitting a database allows the file size limit of 2 Gbytes to be exceeded. For details on limitations for ACCDB files, see, for example, Access 2010 specifications and Access 2016 specifications. Another reason to split a database is for multi-user environments that will benefit from having forms and code in a front-end DB file on users' desktops, with the data in one or more back-end ACCDB files on a networked server. An ACCDB 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 Subtypes not described separately on this site. An indicator at offset 0x14 appears to distinguish between subtypes. Comments welcome.
    Affinity to MDB_family, Microsoft Access MDB File Format Family. The default database file format through Access 2003.

Local use Explanation of format description terms

LC experience or existing holdings Some Library of Congress staff use Microsoft Access as part of their official duties.
LC preference The Library of Congress Recommended Formats Statement (RFS) does not list the Microsoft Access ACCDB file format as preferred or acceptable for acquiring datasets for the its collections because the RFS expresses a preference for platform-independent, non-proprietary, publicly documented formats.

Sustainability factors Explanation of format description terms

Disclosure

Proprietary format developed and managed by Microsoft.

    Documentation

No specification is publicly available.

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. No other software uses ACCDB as a native format.

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 and Advanced Query Tool. Some tools and services focus on repair of Access database files, for example: AccessFIX; Stellar Phoenix Access Database Repair; and EverythingAccess: Database Repair Service.

Viewers for ACCDB files exist for some platforms other than Windows. Such utilities include John Li's ACCDB and MDB Database Managers for Android, iOS, and Mac. and ACCDB MDB Database Pro for Mac, and MDB/ACCDB Viewer for Mac from Egger Apps.

Software toolkits for working with ACCDB files exist for a limited set of programming languages and computing environments. Jackcess is a Java library for reading from and writing to MS Access databases, claiming support through Access 2013. UCanAccess is an open-source Java JDBC driver supporting reading and writing of Microsoft Access databases through ODBC. 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.

    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 ACCDB 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.

Transparency

As a binary format representing a complex data structure, the ACCDB 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/encoded nor compiled into the related ACCDE 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 .accdb 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 ACCDB format usually requires software that can open the particular chronological version of the file format. In some circumstances, a compatible ODBC driver is also required.
Technical protection considerations ACCDB files may be password-protected and/or encrypted. See, for example, Secure Microsoft Access Passwords and Encryption in Access 2007 by Garry Robinson, and Jet MDB security - under the hood and Changing the encryption type in Access 2007 by Wayne Phillips from EverythingAccess.com.

Quality and functionality factors Explanation of format description terms

Dataset
Normal functionality ACCDB 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 ACCDB database file from other applications or remote copies of Access or for extracting all the table definitions and data from an ACCDB file for migration to another platform. Use of ODBC to access content in an ACCDB file requires a functioning version of Microsoft Access that can open the ACCDB 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.

File type signifiers and format identifiers Explanation of format description terms

Tag Value Note
Filename extension accdb
 
Magic numbers ASCII: ....Standard ACE DB
Hex: 00 01 00 00 53 74 61 6E 64 61 72 64 20 41 43 45 20 44 42
This applies to ACCDB files created with versions of Microsoft Access since Access 2007. From Gary Kessler's file signatures page. See also filesignatures.net.
Indicator for profile, level, version, etc. See note.  An indicator of the version of the ACE DB needed to open the file is recorded at offset 0x14 from the beginning of the ACCDB file. For example, the Hex value of 02 appears to indicate that the file can be opened in Access 2007, while the Hex value of 0x05 indicates that Access 2016 (perhaps only as updated in March 2017) is required. Comments welcome.
Pronom PUID See note.  No corresponding match as of May 2017.
Wikidata Title ID Q29907905
See https://www.wikidata.org/wiki/Q29907905

Notes Explanation of format description terms

General

Related File Extensions: In addition to the main database file, with the .accdb extension, Access uses other extensions for related files. See Introduction to the Access 2007 file format | File types. The most significant related extensions are:

  • .accde: used for a "locked-down" version of the original .accdb file. Any Visual Basic for Applications (VBA) code is included in the .accde file only in compiled form. Users working with .accde files cannot make design changes to forms or reports. See, for example, Converting an Access Database to ACCDE File Format. There is no convenient way to convert an ACCDE file back to a fully functional ACCDB database file, although a recovery service is offered by EverythingAccess.com.
  • .accdr: By changing the extension of an ACCDB file to .accdr, the file will open in runtime mode in Windows. Changing the extension back to .accdb restores full functionality for design activities.
  • .accdt: Used for a template for an Access database (starting with Access 2007). The template is a collection of structures used to define a database application including schemas for storing data, the data to be stored, layout descriptions for views of the data, actions controlling workflow, and metadata describing the database application as a whole. In contrast to other Office applications, for which changing the extension of a document file to the template extension (e.g., changing .docx to .dotx) is sufficent to use that document as a template, templates for Access use a completely different format based on OPC/OOXML_2012. This format is fully documented at [MS-ACCDT]: Access Template File Format. For an informal introduction, see How Access uses Templates from The Rational Guide to Microsoft Office Access 2007 Templates. Templates cannot include all features that may be in an ACCDB database; see list of features not supported in ACCDT files in Access 2010 Programmer's Reference.

For a list of all extensions associated with Access, see File Extensions in the Wikipedia entry for Microsoft Access.

Relationship between Access and SharePoint: Microsoft publishes detailed technical specifications for Microsoft protocols that are implemented and used by SharePoint Products and Technologies to interoperate or communicate with other Microsoft products. These include specifications for various protocols and supporting formats used to communicate beween SharePoint and Access, including:

  • [MS-ACCDT]: Access Template File Format. a database template used to create an instance of a database application. The database template data is contained in a ZIP package conforming to the Open Packaging Conventions. Individual files stored in the ZIP package, known as parts, contain information about the structure and content of the resulting database application. The parts include definitions of the database objects, data to be populated, and properties of the resulting database application. Parts contain information stored using XML, text, and binary formats. See Access 2010 Programmer's Reference, page 850 for a diagram of parts in an ACCDT file. Since the specification was originally published in 2007, [MS-ACCDT] has had several minor updates and one associated with Access 2016 that is termed major. However, the change in the February 2016 update appears to relate only to the section listing product versions to which the specification applies, which clearly changed to include Access 2016.
  • [MS-AXL]: Access Application Transfer Protocol Structure. A persistence format for database objects within ACCDT packages. Since the specification was originally published in 2007, [MS-AXL] has had several minor updates and a major one associated with Access 2016.
History

For history of the predecessor MDB format and Microsoft's support of ODBC in Access, see Microsoft Access MDB Format Family.

Microsoft Access Release History since Introduction of ACCDB: Access 2007 introduced the ACCDB file format as the default format and encouraged users to update existing databases to the new format. Major releases since then, inconjunction with releases of the Office Suite for Windows 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. However, judging from Using the Large Number data type, this feature, adding support for storing larger integers prevents use of versions of Access prior to Access 2016.

For the other Office applications, Microsoft has been careful to retain backwards compatibility and application functionality to open old files. This has not been the case for Access database files. As of May 2017, Access 2016 should be able to read all ACCDB files; however, MDB files created earlier than Access 2000 and not upgraded/converted in an intermediate version cannot be opened in Access 2016.


Format specifications Explanation of format description terms


Useful references

URLs


Last Updated: 06/06/2017