What is Microsoft Access?

Microsoft Access^® is the most popular database product on the planet. People argue about what this means exactly, but it is clear that more people use Access (the actual product) than any other database management system.

But many people don't realize that Access is not just one product, but a suite of tools integrated into one product. In other databases, these tools are separate programs, some of which must be purchased from a third-party. Together, these tools make Access one of the most powerful database products in existence.

Now, some may scoff at this. They would say that Access is just a tinker toy compared to a "real" database like Oracle or SQL Server. But that is just ignorance speaking. It is true that the database component of Access (the Jet database engine) is limited in terms of size, speed, and number of users. However, the Jet engine is only one part (and not the most important part, in my opinion) of Access.

So let's look at the component parts of Access:

First of all, as I already mentioned, there is the Jet database engine. In fact, there are two different versions of Jet. There is the traditional version (mdb) which has been around since version 2.0. With Access 2007, there is a new version, the Access Database Engine (accdb). Although it has limitations, it is nonetheless a fully functional relational database management system.

Secondly, there is the Access User Interface (AUI). This is the part of Access that the user sees when they open a table. With the AUI, a user can create tables, modify tables, add data, modify data, and create filters to view data. In SQL Server, this functionality is done in the SQL Server Management Studio (SSMS) or the older Enterprise Manager. In fact, the original Enterprise Manager was patterned after the Access UI.

Third, there is the Query Builder. This is an ad hoc querying tool. Queries can be built in the graphical user interface (gui) or directly in standard SQL. Access does not have "Views" like SQL Server does, but it does have "Select queries" that server the same function. In addition, Access has Data Definition Language (DDL) queries that create and modify table structure, Data Manipulation Language (DML) queries that display or modify data. In SQL Server, you would need a tool like Query Analyser or SSMS to accomplish this.

Another type of query Access uses is the Pass-through Query. A pass-throrgh query bypasses the Jet engine altogether and passes the query on to a linked source like SQL Server. The versatility of Access connet to and manipulate data from various data sources is truly astounding.

Fourth, Access has a built-in application generator. This itself consists of several parts.

1. Forms builder. Access allows you to create form in much the same way as Visual Basic. However, Access forms are much easier to create and the controls available are specific to database development.
2. Report builder. At some point you have to report the data in your database. In Access, you do this in the report builder. In SQL Server, you would have to use a separate product like SQL Server Reporting Services or Crystal Reports depending on how you want them published.
3. VBA modules. All database applications require a procedural language at some point. There are just some things that can't be done in SQL. In Access, this capability is provide in Visual Basic for Applications modules, both general (global) modules and programming behind the forms and reports (class modules).

Creating an application in another database, SQL Server for instance, would require multiple products: SQL Server as the data engine, SSMS to create and modify tables, Query Builder to run ad hoc queries, Visual Basic to create the application, and Crystal Reports for reporting. In Access, you get it all in one.

So you can see that Access is really a Rapid Application Development (RAD) product than it is a database. In fact, Access might more accurately be called an application generator with a complementary database provided.