Microsoft Access

from Wikipedia, the free encyclopedia
Microsoft Access

Microsoft Office Access (2018 - present) .svg
Basic data

developer Microsoft Corporation
Publishing year November 1992
Current  version Access 2019 (Windows)
operating system Microsoft Windows
category Relational database and development environment
License Proprietary
German speaking Yes
Access website

Microsoft Access [ maɪ.kɹoʊ.sɒft æk.sɛs ] (short MS Access , after English access , access' ) is an application within the Microsoft Office family and is as individual Office application or as a component of Office Professional ( office suite ) available.

Access combines the Microsoft Jet Engine as a relational database management system with the tools of an integrated development environment which, with its graphical user interfaces, is particularly suitable for the target group of end users for the production of database applications.

Access supports (with restrictions) the database programming language SQL -92.

Emergence

The success of desktop database applications such as dBASE and Foxpro prompted Microsoft to decide in the mid-1980s to develop its own database application for the then new Windows operating system . The development work under the project name Omega was delayed again and again until the still faulty version 1.0 and shortly afterwards the stable version 1.1 came onto the market in the early 1990s.

The current version is Access 2019. In contrast to the other Office programs Word , Excel and PowerPoint , which are also offered by Apple for the Mac OS X operating system , Access is only available for Windows .

properties

Compiler scenario Access.png

By default, Access stores all data in a database application in a single file of your own mdb - file format (up to version Access 2003) and the accdb -Dateiformates (version Access 2007) from. This includes elements of the user interface as well as the database tables. Alternatively, it is very easy to keep the data (table definitions and the data stock) in different files ( front or back end ) in contrast to the user interface (and other VisualBasic modules and macros, reports, etc. ). When integrating or linking external data sources (tables), different Access versions, but also non-Access formats such as dBASE , as well as many common data sources, e.g. B. can be addressed via ODBC .

In contrast to earlier PC-based database systems, Access supports a relational database model with referential integrity checks . To access externally to Access databases, among other things, also developed by Microsoft is ODBC - programming . Access databases can also be accessed from other programming languages, for example Delphi , Visual Basic, etc. through the use of ADO or the somewhat older DAO, which is tailored to MDBs . To only integrate this format, Access does not need to be licensed or installed. As of Windows 2000, ADO is part of the operating system as part of MDAC . For earlier Windows versions it can be installed free of charge.

Access, which is based on the Microsoft Jet Engine as the database backend , is well suited for small to medium-sized databases with (roughly) up to ten users accessing at the same time. In addition, Microsoft recommends the very simple migration to the MS SQL server. In order to make multi-user access within the mdb file format easy, write access to older versions of an Access mdb database always takes place at the end of the file. Deleted or changed elements remain as "holes" in the file until the Access file is compressed (in the narrower sense it is a defragmentation of the database file itself). Regular compressing is no longer necessary with newer versions. Thanks to step-by-step expanded access techniques, including cache- based access, it was already possible from version 1.0 to achieve considerable access speeds in networks .

The operation of access applications in the LAN is linked to a stable network environment . Even small dropouts can tear off the connection to the backend. Access usually remains stable, only the affected users have to restart their database application. If the hardware and network are stable, reorganization (repair and compression) of the database is only necessary in rare cases. With its mdb file format, Access is stable compared to other file-based databases, although classic SQL servers are usually much more stable. In practice , no problems are to be expected in standard networks, for example on an Ethernet basis.

However , Access is less suitable for use in heterogeneous networks or in WLAN - like all file-based (status-bound) access methods that take place via the file system in competition - database servers (DB2 servers, MS-SQL servers, MySQL servers, etc.) should be used here. ) to be favoured.

In order to overcome these weaknesses, the database access of Access has been based on a SQL engine since the first versions, which enables easy migration to an SQL server. For this purpose, Access from version 2000 has been expanded so that applications can be built directly on a database that is operated on a Microsoft SQL Server , instead of integrating them via ODBC . For this purpose, a new file format with the extension .adp was developed and file access was changed from DAO (Data Access Objects) to the more versatile ADO (Active Data Objects). ADO is much more abstract from the database sources used than its predecessor and therefore has a higher access speed. The extension .adp is no longer supported since Access 2013.

With Access, a free desktop version of Microsoft SQL Server is supplied from version 2000, which can be managed directly via the Access interface. It is therefore not necessary to purchase an additional program license. However, the management options of the SQL server-based functions (e.g. authorizations) are severely limited compared to the full version, which is why the use of the MSSQL server is advisable for larger projects. Since SQL Server 2005 this has only been the case to a limited extent, since Management Studio Express is also offered free of charge with this version.

By providing visual programming objects that are specially optimized with regard to database access, it is possible with Access to create database-based applications within a short period of time ( rapid prototyping ) without extensive programming work (such as in C or C ++ are necessary). It is also possible to use scripts that were created in a special macro language. A development environment for Visual Basic for Applications (VBA) is integrated in Access so that complex applications can also be created . To improve the speed of program execution, the source text created on the basis of VBA can be compiled and as optimized program code, also called "P-Code" (derived from " Pseudocode ", but with a different meaning here) in the database file (identical to the * .MDE ) get saved.

The runtime versions are used to pass on developed databases to users who do not have access . These are created with the Office Developer Edition , which is free or paid depending on the Access version.

Property types

To create a database, the developer creates several types of objects:

  • Tables for storing the data
  • Queries for the preparation (filtering, sorting, etc.) of the data
  • Forms for data entry via screen mask
  • Reports to output the data on the screen or to a printer
  • Macros for easy automation
  • Visual Basic Module for individual programming in Visual Basic for Applications ( VBA )
  • Import and export specifications for formal information when importing or exporting data (e.g. for CSV files )
  • Relationships between tables with information about constraints

The data about these objects (" metadata ") are stored in so-called 'system tables'. These are tables that Access maintains in the same database as the data to be stored; however, they are usually not visible to the user.

Product versions

  • Access 1.0: (First Windows relational database) (for Windows 3.0)
  • Access 2: "Access Developer's Toolkit" (ADT) for Access2
  • Access 95: "Access Developer's Toolkit" (ADT) for Access95
  • Access 97: "Office Developer's Edition" (ODE)
  • Access 2000: "Microsoft Office Developer" (MOD)
  • Access 2002: (XML Support)
  • Access 2003: "Access 2003 Developer Extensions" (including Windows XP themes)
  • Access 2007
  • Access 2010
  • Access 2013
  • Access 2016
  • Access 2019

Information on the date of the respective release, further update versions and information on the content of extensions can be found in.

similar products

literature

Web links

Individual evidence

  1. Tiemeyer, Konopasek: Access 2002 (online) chapter. 1.6 (end users and application areas)
  2. ^ Microsoft Access Database Program. (No longer available online.) Archived from the original on May 25, 2014 ; accessed on November 26, 2015 (English).
  3. SQL Server access as Microsoft Access ADP project is no longer supported from Office 2013 @ codedocu_de Office 365. Retrieved on June 4, 2020 .
  4. https://www.software-matters.co.uk/access-runtime-downloads.html Approved Downloads
  5. Microsoft Access Version Releases, Service Packs, Hotfixes, and Updates History . fmsinc.com; accessed on October 28, 2016