Current Microsoft MVP Awardees

MS Access™ MVP Awardee from July, 2007 through June, 2019


Active Topics

Screen shot of Blog

What's in a Relational Database Application?

Publish Date -- Nov 25, 2020

A properly designed relational database application includes three components:

  • A Data Layer: The data layer consists of the tables in a database, where data is stored. In Access, this is the ACE database engine
  • An Interface Layer: The Interface layer is the collection of objects through which users can interact with their data. In Access, this is the forms and reports.
  • A Logic Layer: The logic layer is the code needed to manage the data and manipulate the user interface objects. Access has two tools for this, VBA and Macros.

Microsoft Access is unique among developer tools in many ways. One of the most important, in my opinion, is that it provides all three of the tools you need to create complete Relational Database Applications in a single development environment. Most other tools used by database developers offer one, or at the most, two of the three tools you need.

A properly designed relational database applications includes three components:

  • A Data Layer: The data layer consists of the tables in a database, where data is stored. In Access, this is the ACE database engine
  • An Interface Layer: The Interface layer is the collection of objects through which users can interact with their data. In Access, this is the forms and reports.
  • A Logic Layer: The logic layer is the code needed to manage the data and manipulate the user interface objects. Access has two tools for this, VBA and Macros.
    The logic layer can be further differentiated into code that manipulates and manages data, and code that manipulates and manages the user interface.

I haven't included queries in one of these categories because I'm not settled in my mind about how to do that. In one sense they are part of the data layer, in that they are built on top of the tables, using the SQL language to select, filter and sort the data. But in another way they work with the interface in that one reason we use queries is to bring data from tables into forms and reports. And in still another way you could see them as logic components in that you use them to insert, modify and delete data. However you look at it though, they fill one or more of those functions in any relational database application.

In contrast, most other development environments offer only one or two of those components. SQL Server, MySQL, PostGres, and so on, are databases with powerful tools for storing and managing data. In some ways, they also incorporate logic elements in that they support Stored Procedures. With stored procedures a developer can write code to manage many data related tasks. However, these database engines do not include user interfce components nor do stored procedures support logic to manipulate a user interface. They are entirely oriented towards the data layer.

Development tools like Visual Studio are aimed at the interface and logic layers of a relational database application. They do not, in themselves, have any components to store data. On the other hand, there are are plethora of coding frameworks and languages, so much so that one of the most challenging aspects of working in this environment can be deciding which of those options to use!

With Access a fairly inexperienced person has a full set of tools readily at hand from day one. Tables, queries, forms and reports, VBA, all at your finger tips in a single file. That's a powerful thing. I wish more people were aware of, and appreciated that about Access.

Three Layer Cake Image by Kevin Phillips from Pixabay

Feedback is welcome and appreciated. ghepworth@gpcdata.com
©Copyright 2010-2020 Grover Park Consulting. All Rights Reserved.
Free Resources and Full Working Demos for Microsoft Access Database Design and Development