Microsoft Access
 | This article needs additional citations for verification. (August 2006) |
| Microsoft Office Access Icon | |
| Microsoft Office Access Screenshot Office Access 2007 running on Microsoft Windows Vista. | |
| Developer(s) | Microsoft |
|---|---|
| Stable release | 12.0.6211.1000 (2007 SP1)
/ December 11, 2007 |
| Operating system | Microsoft Windows |
| Type | RDBMS |
| License | Proprietary EULA |
| Website | Microsoft Office Access Homepage |
Microsoft Office Access, previously known as Microsoft Access, is a relational database management system from Microsoft that 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 (including MySQL and PostgreSQL). 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 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, followed in May of 1993 by an Access 1.1 release to improve compatibility with other Microsoft products.
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 small recordsets but testing showed some circumstances caused data corruption. For example, file sizes over 10 MB were problematic (note that most hard disks were smaller than 500 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's initial codename was Cirrus; the forms engine was called Ruby. This was before Visual Basic - Bill Gates saw the prototypes and decided that the BASIC language component should be co-developed as a separate expandable application, a project 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 by hobby programmers to create ad hoc customized desktop systems for handling the creation and manipulation of data.
Microsoft has strongly recommended for the past decade that Jet databases are inappropriate for basic web based applications hosted on Microsoft's Internet Information Services and utilizing Microsoft Active Server Pages ASP.
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 salespeople. 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.[citation needed] 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 all other ODBC-compliant product.
In addition to traditional ODBC methods, Access also offers "Access Data Projects" for accessing SQL Server data, which provides a number of advantages over linked tables. Unfortunately, there are also several bugs with the technology, and after years of promoting it, Microsoft now appears to be downplaying its use.[citation needed]
Features
One of the benefits of Access from a programmer's perspective is its relative compatibility with SQL (structured query language) — queries can be viewed graphically or edited as SQL statements, and SQL statements can be used directly in Macros and VBA Modules to manipulate Access tables. Users can 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 scaled down version of Microsoft SQL Server 2000, has been a free download for a decade and may be used with Access as an alternative to the Jet Database Engine.
Unlike other RDBMS, Microsoft Access does not implement database triggers or stored procedures.
Starting in Access 2000 (Jet 4.0), there is a new syntax for creating queries with parameters, in a way that looks like creating stored procedures, but these procedures are still limited to one statement per procedure.[2]
In ADP files (supported in Access 2000 and later), the database-related features are geared more towards a client-server architecture with MSDE or Microsoft SQL Server serving as the back-end 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). This centralization of queries and tables in the database server provides a more reliable development environment for most businesses.
Development
Access allows relatively quick development because of very good GUI design tools, and high level integration of GUI design and data objects. 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 obsolete Data Access Objects (DAO), which has not been included in Office, Windows or MDAC for most of the past decade.
ADO has been much more widespread; and this is why it is recommended to upgrade all DAO to ADO (but still accessible) by ActiveX Data Objects (ADO); however (DAO) has been reintroduced as an option in the latest version, Microsoft Access 2007.
Beside DAO and ADO, developers can also use OLE DB and ODBC for developing native C/C++ programs for Access.[3]
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.[4] Except in VBA, 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.
Microsoft Access can be applied to small projects (the Access 97 speed characterization was done for 32 users)[5]but scales poorly to larger projects with more than several 10MB of data or many users because of the way indexing and locking are handled. As a Microsoft Access database can be cached locally when used on network, processing speed may be substantially better when there is only a single user. Because of the effect of packet latency on the record locking system, Access databases are effectively too slow to be used on a Virtual Private Network or a Wide Area Network. Access Data Projects work great over VPN and WAN.
Access includes an Upsizing Wizard that allows users to upsize their database to Microsoft SQL Server if they want to move to an ODBC client-server database.
One recommended technique is to migrate to SQL Server and utilize Access Data Projects. This allows stored procedures, views, and constraints using standard SQL. Additionally this full client-server design significantly reduces maintenance and availability 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). This technique also allows the developer to divide the application among different files.
Protection
If the database design needs to be secured to prevent changes, Access databases can be locked/protected (and the source code compiled) by converting the database to an .MDE file. All changes to the VBA project (forms or reports) 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 formats:
| File format | Extension |
|---|---|
| Access Project | .adp |
| Access Blank Project Template | .adn |
| Access Database (2007) | .accdb |
| Access Database Runtime (2007) | .accdr |
| Access Database Template (2007) | .accdt |
| Access Database (2003 and earlier) | .mdb |
| Access Database, used for addins (2,95,97), previously used for workgroups (2). | .mda |
| Access Workgroup, database for user-level security. | .mdw |
| Access (SQL Server) detached database (2000) | .mdf |
| Protected Access Database, with compiled VBA (2003 and earlier) | .mde |
| Protected Access Database, with compiled VBA (2007) | .accde |
| Windows Shortcut: Access Macro | .mam |
| Windows Shortcut: Access Query | .maq |
| Windows Shortcut: Access Report | .mar |
| Windows Shortcut: Access Table | .mat |
| Windows Shortcut: Access Form | .maf |
Versions
| Date | Version | Version number |
Supported OS | Office suite version |
|---|---|---|---|---|
| 1992 | Access 1.1 | Windows 3.1x | ||
| 1993 | Access 2.0 | Windows 3.1x | Office 4.3 Pro | |
| 1995 | Access for Windows 95 | Windows 95 | Office 95 Professional | |
| 1997 | Access 97 | Windows 9x, NT 3.5/4.0 | Office 97 Professional and Developer | |
| 1999 | Access 2000 | Windows 9x, NT 4.0, 2000 | Office 2000 Professional, Premium and Developer | |
| 2001 | Access 2002 | Windows 98, Me, 2000, XP | Office XP Professional and Developer | |
| 2003 | Access 2003 | Windows 2000, XP | Office 2003 Professional and Professional Enterprise | |
| 2007 | Microsoft Office Access 2007 | Windows XP SP2, Vista | Office 2007 Professional, Professional Plus, Ultimate and Enterprise |
There are no Access versions between 2.0 and 7.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 compatible with Word 7.
Competing software
- Amiga Datastore
- Alpha Five
- Borland Paradox
- Datawasp
- dBase
- FileMaker Pro (formerly Claris Filemaker)
- Kexi
- Lotus Approach
- NeoOffice
- OpenOffice.org Base
- Oracle XE (Express Edition)
- Sun StarBase
- Foxpro (later acquired by Microsoft)
- SQLite
- Sybase PowerBuilder
- MySQL
- PostgreSQL
- Omnis Studio
External links
- Access Team Blog
- Microsoft Access Home Page
- Microsoft Access Newsgroups
- A FAQ site about Microsoft Access
- Microsoft Access for Beginners
Database protection
References
- ^ Where did the name for Microsoft Access come from?
- ^ Dev City
- ^ Aleksandar Jakšić (2008). "Developing Access 2007 Solutions with Native C or C++". Microsoft Corporation. Retrieved 2008-09-22.
{{cite web}}: Unknown parameter|month=ignored (help) - ^ Naming Conventions for Microsoft Access
- ^ [Kevin Collins (Microsoft Jet Program Management), "Microsoft Jet 3.5 Performance Overview and Optimization Techniques", MSDN. Retrieved July 19, 2005.]
This article is based on material taken from the Free On-line Dictionary of Computing prior to 1 November 2008 and incorporated under the "relicensing" terms of the GFDL, version 1.3 or later.
| Types | |
|---|---|
| Concepts | |
| Objects | |
| Components | |
| Functions | |
| Related topics | |
