Information about Microsoft Access

Microsoft Office Access
Office Access 2007
Developer:	Microsoft
Latest release:	12.0.4518.1014 / November 6, 2006
OS:	Microsoft Windows
Genre:	RDBMS
License:	Proprietary EULA
Website:	Access Home Page - Microsoft Office Online

Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft which combines the relational Microsoft Jet Database Engine with a graphical user interface and software development tools. It is a member of the 2007 Microsoft Office system.

Access can use data stored in Access/Jet, Microsoft SQL Server, Oracle, or any ODBC-compliant data container. Skilled software developers and data architects use it to develop application software. Relatively unskilled programmers and non-programmer "power users" can use it to build simple applications. It supports some object-oriented techniques but falls short of being a fully object-oriented development tool.

Access was also the name of a communications program from Microsoft, meant to compete with ProComm and other programs. This Access proved a failure and was dropped.^[1] Years later Microsoft reused the name for its database software.

History

Access version 1.0 was released in November 1992.

Microsoft specified the minimum operating system for Version 2.0 as Microsoft Windows v3.0 with 4 MB of RAM. 6 MB RAM was recommended along with a minimum of 8 MB of available hard disk space (14 MB hard disk space recommended). The product was shipped on seven 1.44 MB diskettes. The manual shows a 1993 copyright date.

The software worked well with very large records sets but testing showed some circumstances caused data corruption. For example, file sizes over 700 MB were problematic. (Note that most hard disks were smaller than 700 MB at the time this was in wide use.) The Getting Started manual warns about a number of circumstances where obsolete device drivers or incorrect configurations can cause data loss.

Access' initial codename was Cirrus. This was developed before Visual Basic and the forms engine was called Ruby. Bill Gates saw the prototypes and decided that the Basic language component should be co-developed as a separate expandable application. This project was called Thunder. The two projects were developed separately as the underlying forms engines were incompatible with each other; however, these were merged together again after VBA.

Uses

Access is used by small businesses, within departments of large corporations, and hobby programmers to create ad hoc customized desktop systems for handling the creation and manipulation of data. Access can be used as a database for basic web based applications hosted on Microsoft's Internet Information Services and utilizing Microsoft Active Server Pages ASP. Most typical web applications should use tools like ASP/Microsoft SQL Server or the LAMP stack.

Some professional application developers use Access for rapid application development, especially for the creation of prototypes and standalone applications that serve as tools for on-the-road salesmen. Access does not scale well if data access is via a network, so applications that are used by more than a handful of people tend to rely on Client-Server based solutions. However, an Access "front end" (the forms, reports, queries and VB code) can be used against a host of database backends, including JET (file-based database engine, used in Access by default), Microsoft SQL Server, Oracle, and any other ODBC-compliant product.

Features

One of the benefits of Access from a programmer's perspective is its relative compatibility with SQL (structured query language) —queries may be viewed and edited as SQL statements, and SQL statements can be used directly in Macros and VBA Modules to manipulate Access tables. In this case, "relatively compatible" means that SQL for Access contains many quirks, and as a result, it has been dubbed "Bill's SQL" by industry insiders. Users may mix and use both VBA and "Macros" for programming forms and logic and offers object-oriented possibilities.

MSDE (Microsoft SQL Server Desktop Engine) 2000, a mini-version of MS SQL Server 2000, is included with the developer edition of Office XP and may be used with Access as an alternative to the Jet Database Engine.

Unlike a complete RDBMS, the Jet Engine lacks database triggers and stored procedures. Starting in MS Access 2000 (Jet 4.0), there is a syntax that allows creating queries with parameters, in a way that looks like creating stored procedures, but these procedures are limited to one statement per procedure.[1] Microsoft Access does allow forms to contain code that is triggered as changes are made to the underlying table (as long as the modifications are done only with that form), and it is common to use pass-through queries and other techniques in Access to run stored procedures in RDBMSs that support these.

In ADP files (supported in MS Access 2000 and later), the database-related features are entirely different, because this type of file connects to a MSDE or Microsoft SQL Server, instead of using the Jet Engine. Thus, it supports the creation of nearly all objects in the underlying server (tables with constraints and triggers, views, stored procedures and UDF-s). However, only forms, reports, macros and modules are stored in the ADP file (the other objects are stored in the back-end database).

Development

Access allows relatively quick development because all database tables, queries, forms, and reports are stored in the database. For query development, Access utilizes the Query Design Grid, a graphical user interface that allows users to create queries without knowledge of the SQL programming language. In the Query Design Grid, users can "show" the source tables of the query and select the fields they want returned by clicking and dragging them into the grid. Joins can be created by clicking and dragging fields in tables to fields in other tables. Access allows users to view and manipulate the SQL code if desired.

The programming language available in Access is, as in other products of the Microsoft Office suite, Microsoft Visual Basic for Applications. Two database access libraries of COM components are provided: the legacy Data Access Objects (DAO), which was superseded for a time (but still accessible) by ActiveX Data Objects (ADO); however (DAO) has been reintroduced in the latest version, MS Access 2007.

Many developers who use Access use the Leszynski naming convention, though this is not universal; it is a programming convention, not a DBMS-enforced rule.^[2] It is also made redundant by the fact that Access categorises each object automatically and always shows the object type, by prefixing Table: or Query: before the object name when referencing a list of different database objects.

MS Access can be applied to small projects but scales poorly to larger projects involving multiple concurrent users because it is a desktop application, not a true client-server database. When a Microsoft Access database is shared by multiple concurrent users, processing speed suffers. The effect is dramatic when there are more than a few users or if the processing demands of any of the users are high. Access includes an Upsizing Wizard that allows users to upsize their database to Microsoft SQL Server if they want to move to a true client-server database. It is recommended to use Access Data Projects for most situations.

Since all database queries, forms, and reports are stored in the database, and in keeping with the ideals of the relational model, there is no possibility of making a physically structured hierarchy with them.

One recommended technique is to migrate to SQL Server and utilize Access Data Projects. This allows stored procedures, views, and constraints - which are greatly superior to anything found in Jet. Additionally this full client-server design significantly reduces corruption, maintenance and many performance problems.

Access allows no relative paths when linking, so the development environment should have the same path as the production environment (though it is possible to write a "dynamic-linker" routine in VBA that can search out a certain back-end file by searching through the directory tree, if it can't find it in the current path). This technique also allows the developer to divide the application among different files, so some structure is possible.

Protection

If the database design needs to be secured to prevent from changes, Access databases can be locked/protected (and the source code compiled) by converting the database to an .MDE file. All changes to the database structure (tables, forms, macros, etc.) need to be made to the original MDB and then reconverted to MDE.

Some tools are available for unlocking and 'decompiling', although certain elements including original VBA comments and formatting are normally irretrievable.

File extensions

Microsoft Access saves information under the following file extensions:

.mdb - Access Database (2003 and earlier)

.mde - Protected Access Database, with compiled macros (2003 and earlier)

.accdb - Access Database (2007)

.mam - Access Macro

.maq - Access Query

.mar - Access Report

.mat - Access Table

.maf - Access Form

.adp - Access Project

.adn - Access Blank Project Template

References

Further reading

• "New life for old Microsoft Access data"
• "Microsoft Office Access 2003 Inside Out" by John L. Viescas
• "Special Edition Using Microsoft Office Access 2003" by Roger Jennings
• "Database Design for Mere Mortals" by Michael J. Hernandez
• "Access Database Design & Programming" by Steven Roman
• "Access 2002 Developer's Handbook™ Set By Paul Litwin; Ken Getz; Mike Gunderloy
• "Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach" by Karin Bast, Leon Cygman, Gerard Flynn, Rebekah Tidwell
• "Access 2003 VBA Programmer's Reference" by Patricia Cardoza, Teresa Hennig, Graham Search, Armen Stein
• "SQL Access to SQL Server" Published by Apress - Susan Sales Harkins and Martin WP Reid
• "Mastering Microsoft Office Access 2003" Published by Sams - Alison Balter
• "White paper: Database Evolution: Microsoft® Access within an Organization's Database Strategy by Luke Chung"

Versions

date	version name	version number	supported OS	corresponding office suite
1992	Access 1.1	1	Windows
1993	Access 2.0	2.0	Windows	Office 4.3 Pro
1995	Access for Windows 95	7.0	Windows 95	Office 95 Pro
1997	Access 97	n/a	Windows, others	Office 97 Pro
1999	Access 2000	9.0	Windows, others	Office 2000 Pro and Premium
2001	Access 2002	10	Windows, others	Office XP
2003	Access 2003	11	Windows, others	Microsoft Office System 2003
2007	Microsoft Office Access 2007	12	Windows, others	2007 Microsoft Office system

There is no Access 3.0 to 6.0 because the Windows 95 version was launched with Word 7. All of the Office 95 products have OLE 2 capabilities, and Access 7 shows that it was contemporary with Word 7.

Competing software

• Amiga Datastore
• Alpha Five
• Borland Paradox
• dBase
• FileMaker Pro (formerly Claris Filemaker)
• Kexi
• Lotus Approach
• NeoOffice
• OpenOffice.org Base
• Sun StarBase
• Sybase PowerBuilder

External links

• Microsoft Access Home Page
• Microsoft Access Newsgroups
• A FAQ site about Microsoft Access
• Microsoft Access for Beginners

Database Protection

• How to Make Design Changes to an .MDE

•  • [ e] Microsoft Office
Desktop products:	Accounting • Access • Communicator • Document Imaging • Document Scanning • Entourage • Equation Editor • Excel • Groove • InfoPath • InterConnect • MapPoint • OneNote • Outlook • Picture Manager • PowerPoint • Project • Publisher • SharePoint Designer • Visio • Word
Server products:	Communications Server • Forms Server • Groove Server • PerformancePoint Server • Project Server • Project Portfolio Server • SharePoint Server
Online services:	Live Meeting • Office Live
Discontinued products:	Binder • Data Analyzer • FrontPage • Mail • The Office Assistants • PhotoDraw • Photo Editor • Schedule+ • Vizact Bold type indicates a core application

This article was originally based on material from the Free On-line Dictionary of Computing, which is licensed under the GFDL.

Topics in database management systems (DBMS)  •  • [ edit]
Concepts Database  Database models  Database storage  Relational model  Distributed DBMS  ACID  Null Relational database  Relational algebra  Relational calculus  Database normalization  Referential integrity  Relational DBMS  Primary key, Foreign key, Surrogate key, Superkey, Candidate key
Objects Trigger  View  Table  Cursor  Log  Transaction  Index  Stored procedure  Partition	Topics in SQL Select  Insert  Update  Merge  Delete  Join  Union  Create  Drop Begin work  Commit  Rollback  Truncate  Alter
Implementations of database management systems
Types of implementations Relational  Flat file  Deductive  Dimensional  Hierarchical  Object oriented  Object relational  Temporal  XML data stores
Database products Object-oriented (comparison)  Relational (comparison)	Components Query language  Query optimizer  Query plan  ODBC  JDBC