August 3, 2013 in Microsoft Access
The fact is, most people don’t understand what Microsoft Access is. That includes most developers. This fact struck me in particular at the start of a recent project overhauling a collection of legacy Access applications. Naturally the end users were pretty murky on what Access was, they just knew they had to open Access to get to their applications. The business analyst who had been managing a lot of configuration and building queries and reports was certainly not a developer, and was not well versed in how Access applications are (or can be) architected. My colleague and lead on the project, a competent and experienced developer in both .Net and hardware control languages, also hadn’t really seen Access since college, where it was just presented as a “little database” for teaching RDBMS concepts.
I ended up spending a hour and a half giving my developer colleague a crash course in what Access really is, from my perspective, and how Access application development should be done. That gave us a much better common foundation to begin planning our project. Despite the ubiquity of Microsoft Access I believe this understanding is as rare as it is useful. And so I begin a series here on Access fundamentals from a developer’s perspective. We’ll start today with the most basic question: What is Microsoft Access?
The Many Aspects of Microsoft Access
Microsoft Access is really several things bundled together. For comparison, I also present the equivalent enterprise software that fulfills the same purpose, both for the typical Microsoft stack and for a comparable Oracle stack.
|Aspect||Microsoft Enterprise Stack||Oracle Stack|
|A database engine||SQL Server||Oracle database engine|
|A database management GUI application||SQL Server Management Studio||PL/SQL Developer|
|A runtime host (for VB)||CLR (for C#, VB.NET, F#, etc)||Java runtime (for Java)|
|An IDE (for VB)||Visual Studio||Eclipse|
|A forms-based application framework||Windows Forms or WPF||Swing|
|A report-creation application||SQL Server Reporting Services||Crystal Reports|
As the primary target audience is non-developer business users, Microsoft doesn’t distinguish these parts. And in fact the architecture of Access itself blurs the lines between these areas at times. But I believe it is useful to distinguish between these parts, especially when developing high quality applications in Microsoft Access — something that actually can be done, despite what some people may think!
The Access Database Engine
If nothing else, Access is often described as “a database.” A database engine is certainly included in Microsoft Access, and forms the core of most Access based applications.
A little history
Before Microsoft Access 2007, the independent existence of the database engine was even more clear, as it was distributed with the Windows operating system as part of the Microsoft Data Access Components (MDAC) framework. The engine was the Jet engine, built around the Rushmore query optimization algorithms inherited from FoxPro. Microsoft encouraged developers in C++ and VB to use the Jet engine, along with its APIs available through both DAO and ADO, as the in-process relational data store of choice for data-driven applications. Neither developers nor users needed Microsoft Access, or even the Microsoft Access runtime, to use the Jet engine.
By 2007 Microsoft had shifted its developer focus to the .Net platform as a whole and to the various SQL Server engine versions as the database of choice. MDAC had been deprecated, and SQL Server Compact was presented as the new option for in-process, file-based relational data embedded in applications. The Access team had been working on their own engine, though, and with the introduction of Access 2007 came a full rewrite of the engine-formerly-known-as-Jet.
The new ACE engine
The new engine is the ACE engine (the stated meaning of the letters “ACE” varies). I won’t get into the details of what’s different, apart from the fact that it is no longer distributed with the Windows operating system. It’s still free to download and use, though, fully independent of Microsoft Office.
The Access Database Management GUI
This one is simple; it’s the Access GUI components for editing tables and “queries”, as both views and stored procedures are called in Access.
The VB Runtime and IDE
Like most Office applications, Microsoft Access includes a full VB runtime instance and the IDE to create VB projects. And let’s get something clear: Visual Basic for Applications is the same language as “classic” VB, the last version being VB6. It uses the same executable format (P-code) and the same runtime (the VB runtime). The compiler is just slightly different, and does not provide the option of producing standalone .exe or .dll files. Even the IDE is almost identical to the VB6 editor of Visual Studio 6.0.
In fact the Office team hasn’t let VB rust, either, and introduced a new version with Office 2010. The new version would be VB7, but as it’s only distributed with Microsoft Office it’s called VBA7, and the runtime dll changed from the inherited msvbvm60.dll to just vbe7.dll. The biggest change with VBA7 is that it is fully 64-bit compatible. For lots more about VB[A] and its runtime, check out my VBA Reference.
Forms-Based Application Framework
Access forms are the most familiar and widely-used components of Access-based applications. While they are similar to the old Microsoft Forms library (and can use components from that library), they are their own thing. Reflecting their tight integration with a database platform, many of the built-in properties in Access forms and controls relate to data binding. The “Data Sheet” form type in Access serves the same purpose as the Data Grid View control in Windows Forms .NET.
Report Creation Application
Finally, Access provides a report creation system. In fact, I think this one aspect of Microsoft Access is the most unique and difficult to replace. While Crystal Reports still hang around, now under a new owner (SAP), and SQL Server Reporting Services introduces a native .Net + SQL Server reporting solution, nothing beats Microsoft Access for really easy plug-and-play, drag-and-drop report creation.
Blurring the lines
Identifying and using all these distinct aspects is useful and even essential for high quality application development using Microsoft Access (or for trying to untangle an inherited mess). But the lines do blur in many places. For example, some types of data types supported by the database engine can only be created using the management GUI. The database engine can also make direct use of VB procedures executed by the runtime (similar to the embedded CLR in SQL Server) — but only when the Access host application is running. Reports and especially forms are conceptually independent of any backing database, but the omnibus Access file format (and the user interface) include these objects in a database instance right along with SQL objects (tables and queries). Finally, tables and queries are conceptually non-visual data objects, but Access allows users to apply visual formatting to both tables and views (“select queries” in Access lingo) as if they were spreadsheets, and stores these visual properties right on the native database engine objects (DAO TableDefs, QueryDefs, and Field objects).