Microsoft Access Database
The Microsoft Access Database is the standard database - format of Microsoft Access . The standard file extension is ".mdb" (since Access 2007 also ".accdb"). MDB is the abbreviation for Microsoft DataBase and a file format that represents a file-based proprietary database. The internal structure is largely only known to Microsoft . Programmers can use this database via the Jet Engine or ActiveX Data Objects .
General
After Microsoft Access (code name "CIRRUS") appeared in 1992, it had replaced the leading database format dBase in four years. From version 1.0 (1992) the database format offers: Tables, Referential Integrity, Check Clauses, Encryption, User Management, Dialogs, Reports, Stored Queries and VBA Interaction (initially called AccessBasic).
There are numerous applications that use this database format as it is also provided with Microsoft's programming tools. A modified form of this file format is also used in Microsoft MapPoint , or as a so-called Blue-Jet in Microsoft Exchange and z. B. for the Windows Server Active Directory database. A previous version of this format was delivered from 1989 with Microsoft Basic Professional Development System (PDS) Version 7.0 and 7.1 as well as Microsoft Visual Basic for Dos Version 1.0 as a so-called ISAM database (sample database BOOKS.MDB) including processing tools.
There are already many different versions, some of which are not compatible with each other. An import filter or converter is therefore often required for use.
Microsoft always speaks of Access Database. More precisely - depending on the Access version - different databases are involved. Access uses an existing database (so to speak as a client) to store its extra structures:
- no Access version uses the Jet database version 0.5 (ISAM mdb)
- Access version 1.0 uses the Jet database version 1.0
- Access version 1.1 uses the Jet database version 1.1
- Access version 2.0 uses the Jet database version 2.0
- Access version 7.0 uses the Jet database version 3.0
- Access version 8.0 uses the Jet database version 3.0
- Access version 9.0 uses the Jet database version 4.0 (mdb) or the SQL server (adp)
- Access version 10.0 uses the Jet database version 4.0 (mdb) or the SQL server (adp)
- Access version 11.0 uses the Jet database version 4.0 (mdb) or the SQL server (adp)
- Access version 12.0 uses the Jet database version 4.0 (mdb) or the Jet database version 12.0 (accdb) - requires ACE (Microsoft Access Database Engine 2010)
- Access version 14.0 uses the Jet database version 4.0 (mdb) or the Jet database version 12.0 (accdb) - requires ACE (Microsoft Access Database Engine 2010)
Versions
Name / alias | Exe-type bits | Publishing year | description |
"Access 0.5" | 16 | 1990 | contained in Microsoft BASIC Professional Development System (PDS), 16 bit, DOS-ISAM database, maximum size 128 MB, page size 2 KB, local character set |
Access 1.0 | 16 | 1992 | selectable character set per field |
Access 1.1 | 16 | 1993 | maximum database size 1 GB |
Access 2.0 | 16 | 1994 | included in Office Professional 4.3 |
Access 7.0 / 95 | 32 | 1995 | included in Office 95, 32-bit |
Access 8.0 / 97 | 32 | 1997 | included in Office 97 |
Access 9.0 / 2000 | 32 | 2000 | included in Office 2000, maximum database size 2 GB, page size 4 KB, Unicode (UCS2 or UTF-8? with UnicodeCompression) |
Access 10.0 / 2002 / XP | 32 | 2002 | included in Office XP |
Access 11.0 / 2003 | 32 | 2003 | included in Office 2003 |
Access 12.0 / 2007 | 32 | 2007 | included in Office 2007 |
Access 14.0 / 2010 | 32/64 | 2010 | included in Office 2010 |
Access 15.0 / 2013 | 32/64 | 2013 | included in Office 2013 |
The “Access 0.5” version (with DOS-ISAM database) does not yet contain any graphic tools, only the Jet tables (example database: BOOKS.MDB).
System tables
Depending on the version of the Jet Engine and the Access version above, different system tables are created:
Access / Jet System tables and creators by version | Jet 0.5 | Jet 1.0 | Jet 1.1 | Jet 2.0 | Jet 3.0 | Jet 4.0 | Jet 12.0 | Acc 1.0 1992 | Acc 1.1 1993 | Acc 2.0 1994 | Acc 7.0 1995 | Acc 8.0 1997 | Acc 9.0 2000 | Acc 10.0 2002 XP f2k | Acc 10.0 2002 XP f2k2 | Acc 11.0 2003 f2k | Acc 11.0 2003 f2k2 | Acc 12.0 2007 f2k | Acc 12.0 2007 f2k2 | Acc 12.0 2007 f2k7 | Acc 14.0 2010 | content |
Use jet | 1.0 | 1.1 | 2.0 | 3.0 | 3.0 | 4.0 | 4.0 | 4.0 | 4.0 | 4.0 | 4.0 | 4.0 | 12.0 | ? | ||||||||
Exe-type bits | 16 | 16 | 16 | 16 | 32 | 32 | 32/64 | 16 | 16 | 16 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32 | 32/64 | |
MSysObjects | X | X | X | X | X | X | X | Main objects + diagrams | ||||||||||||||
MSysColumns | X | X | X | X | columns | |||||||||||||||||
MSysIndexes | X | X | X | X | Indices | |||||||||||||||||
MSysRelationships | X | X | X | X | X | X | X | Foreign key | ||||||||||||||
MSysTempIndexes | X | ? | ||||||||||||||||||||
MSysQueries | X | X | X | X | X | X | Query components | |||||||||||||||
MSysACEs | X | X | X | X | X | X | Object access rights | |||||||||||||||
MSysGroups | X | X | X | X | X | ? | Group accounts | |||||||||||||||
MSysAccounts | X | X | X | X | X | ? | User accounts | |||||||||||||||
MSysComplexColumns | X | ? | ||||||||||||||||||||
MSysMacros | X | X | X | Macros | ||||||||||||||||||
MSysIMEXSpecs | O | O | O | X | O | O | O | O | O | O | O | O | O | ? | Import-export jobs | |||||||
MSysIMEXColumns | O | O | O | X | O | O | O | O | O | O | O | O | O | ? | Import-export columns | |||||||
MSysModules | X | X | Module ++ | |||||||||||||||||||
MSysToolbars | X | Toolbars | ||||||||||||||||||||
MSysModules2 | X | Module ++ | ||||||||||||||||||||
MSysCmdbars | O | Toolbars | ||||||||||||||||||||
MSysAccessObjects | X | X | X | X | ? | binary from rpt mcr bas | ||||||||||||||||
MSysAccessStorage | X | X | X | X | ? | binary from rpt mcr bas | ||||||||||||||||
MSysAccessXML | ? | ? | ? | ? | ? | ? | ? | |||||||||||||||
MSysNavPaneGroupCategories | X | X | X | ? | ? | |||||||||||||||||
MSysNavPaneGroups | X | X | X | ? | ? | |||||||||||||||||
MSysNavPaneObjectIDs | X | X | X | ? | ? | |||||||||||||||||
MSysNavPaneGroupToObjects | X | X | X | ? | ? | |||||||||||||||||
Legend
|
Extension to SQL server
An MDB is unsuitable as a backend for larger amounts of data, as its size can currently be a maximum of 2 GB and the access time increases considerably when using several computers at the same time. A proven rule of thumb says: a maximum of 20 users and a maximum of 200 MB.
Microsoft has tried to compensate for this disadvantage by delivering the so-called MSDE (Microsoft Data or Desktop Engine) with Office 2000 or Visual Studio from version 2000, a free version of Microsoft SQL Server with a limited scope of performance . With SQL Server 2005, Microsoft renamed MSDE to Express Edition. Microsoft has also relaxed the restrictions from version to version, the current SQL Server 2016 Express Edition supports 4 processor cores, one GB RAM and a maximum database size of 10 GB.
The conversion to SQL Server as the backend database has always been possible with the so-called "Upsizing Wizard", which is built in from version 2000 under Extras | Database Utilities | Upsizing wizard. This is no longer available since version 2013. However, the transfer can be performed using the Microsoft SQL Server Migration Assistant 8.6 for Access (SSMA). Instructions for this can be found at Microsoft.
Web links
- SQL 2014 Express Edition - Microsoft SQL Server 2014
- Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Version A - Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) Version A
- Access Tips 01 - Allen Browne's Database and Training
- MDB-Tools - Collection of open source tools and libraries for Access databases
- Microsoft Jet Database Engine - English: History of the Jet Engine
- Microsoft Jet Engine - English: Microsoft_Jet_Engine
Individual evidence
- ↑ Article in PC-Welt , May 1996 issue
- ↑ Functions supported by the SQL Server 2016 editions in the msdn