Access: What Is It?
What exactly is Access?
Access is a powerful desktop or file-based database engine. Able to hold millions of records, supports referential integrity, provides unique and multi-field indexing, cascades changes and deletions throughout the data store, embeds attachments, and much more, -- Access' Jet engine packs one mighty little punch, -- all while being self-contained in a single file.
It is also a RAD (Rapid Application Development) tool used to build complex user interfaces. Need to visualize data? Access is the tool for you. Able to provide rich user interfaces, exposes an intuitive designer surface, consume ActiveX and COM objects, embed graphics and charts, and natively bind data sources to forms and reports, -- Access is a powerful and fast application builder.
It is a data integrator that can easily attach to and manipulate information from many different sources. Have disparate data sources? Need to use them as one? Enter Microsoft Access. Hands down the easiest data aggregator on the block. Able to connect effortlessly to SQL Server, MySQL, Oracle, SharePoint, Outlook, Excel, Paradox, any ODBC enabled data store, even text files -- you'll never look back.
It is a powerful Report Writer. Able to produce complex reports with embedded graphics, charts, grouping levels, aggregation, and much more. Need to impress your boss? Access' report writer can churn out professional and amazing looking reports in no time flat.
With power comes responsibility
Mention the name "Access" in an IT crowd and you're likely to get mixed feelings about its usefulness. It's very unfortunate that this product has been the victim of urban myth for so long. Views range from it being a "toy" database, to it being unsecure, or simply that is a nightmare to administer. While it does have its limitations, Access can be a very powerful tool in the hands of the skilled developer.
Microsoft Access (now branded Microsoft Office Access since version 2007) is part of the Office Suite of products. This packaging strategy puts Access on the desktops of millions of users around the globe. With this high level of availability, it's no wonder why it gets used -- and unfortunately abused -- by so many. But I suppose that is true of anything. Put a powersaw and a nailgun in the hands of the inexperienced and who knows what you'll end up with!
Access can also be distributed as a RunTime version. This means that even users who do not have the Office Suite or Access can use a fully functional application.
Encased in that single .MDB file (or .ACCDB in Access 2007 and above) are really two separate and distinct things:
The thing that confuses most people is that either component can be used independently or in unison. In other words, you can just use the database part of Access and build the GUI with another language like VB.NET, or you can use the GUI designer and attach to a different data store, such as SQL Server, or you can use Access as the complete package, both the frontend and the backend.
Let's define a few things before moving on.
The onscreen interface between the user and the computer. This is what the user sees and interacts with as they use the product. Included in it are all of the forms and reports that they may click, select, type in, view, etc.
The temporary data ( tables ) store itself ( like Backend ), but located in Frontend.
The data store itself. This establishes the architecture and relationships involved in whatever process or product is being modeled, and houses all of the data or records that describe it.
These two terms are used quite frequently when describing a complete database system. You may also hear the terms client and server, which can be loosely interpreted the same way.
Once this fundamental difference is understood, we can start to understand why a certain level of confusion arises.
The Main Components
When you open an Access database, you'll see six main components that may or may not be readily apparent.
This is the data storage side of access. All of the table and field definitions and the data itself reside here.
Think of queries as the place to go when you want to ask a question about the data. "How many widgets were produced within the month of May?" type stuff. Queries are also used to interact with the data, Update, Append, and Delete. Jet-SQL, a language in an of itself is used to accomplish this.
Forms are the first stop for your users. They are the data presentation layer of the database, the GUI. Regardless of how the data is stored, forms provide a way to intuitively display and manipulate the data in the tables.
Rather self explanatory. Reports are the hard-copy way to display data, even if that means you simply want it exported to .PDF files and emailed to someone else.
Macros have long been the stepchild of Access. If a repetitive task needs to be done, macros can help you here.
Long time developers usually have down-played their usefulness because they lacked error trapping capabilities. However, in Access 2007 and above, they take on a whole new life, being brought into the limelight for their ability to provide robust functionality including trigger-like behavior with data.
Modules are where complex code is written. Here is where the full power of VBA (Visual Basic for Applications) can be brought to bear. Encapsulation of business logic, custom functions and subroutines, custom classes, and many other goodies can be found here. Limited only by your imagination and ingenuity VBA is where serious developers turn to get things done.
Put it all together and you have a very flexible, dynamic, powerful, comprehensive, and easy to use, development platform. Not many other packages give you such a rich, complete environment to work with, all contained within a single file.
If you give it a chance, you may well find that it could be just the solution you're looking for.
|This page has been accessed 4,676 times. This page was last modified 11:26, 14 February 2012 by Jack Leach. Contributions by Walter Niesz and pacala_ba Disclaimers|