Microsoft Access is a relational database management system from Microsoft which combines the relational Microsoft Jet Database Engine with a graphical user interface. It is a member of the Microsoft Office suite of applications, included in the Professional and higher editions or sold separately.
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. Years later Microsoft reused the name for its database software.
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. 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 an 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).