Microsoft Access Database

from Wikipedia, the free encyclopedia

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
  • X = always
  • O = optional
  • ? = Todo
  • Acc = Access
  • f2k = format 2000
  • f2k2 = format 2002 XP
  • f2k7 = format 2007

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

Individual evidence

  1. Article in PC-Welt , May 1996 issue
  2. Functions supported by the SQL Server 2016 editions in the msdn