Understanding the Roles Access Plays in the Application Design Model

This article explores the many different roles Access can take in an application design, explaining the pros and cons of each method.

The Front-End and Back-End as Access Database Files

One option when developing Access applications is to use Access as both the front-end and the back-end. The Access database is not acting as a true back-end because it is not doing any processing. Figure 1 shows the architecture in this scenario. The Access application resides on the workstation. Access uses the Access Database Engine to communicate with data stored in an Access database file stored on the file server.



The Front-End as an Access Database File Using Links to Tables Stored on the Back-End

In the second scenario, you can link the back-end tables to the front-end application database (.MDB or .ACCDB). The process of linking to back-end tables is almost identical to that of linking to tables in other Access databases or to external tables stored in FoxPro, or other database formats. You can also treat the linked tables like any other linked tables. Access uses ODBC to communicate with the back-end tables (see Figure 2). For example, when you build an Access query, your application sends an Access SQL statement to the Access Database Engine, which translates the statement into ODBC SQL. The Access Database Engine sends this ODBC SQL statement to the ODBC Manager, which locates the correct ODBC driver and passes it the ODBC SQL statement. Supplied by the back-end vendor, the driver translates the statement into the Back-End's specific dialect. The ODBC Manager sends this now back-end specific query to the SQL server and to the appropriate database. Although this may seem cumbersome, a properly designed Access Front-End accessing data stored in a SQL Server database is quite efficient. I have proven this over and over again with enterprise-wide applications written in Microsoft Access.


The Front-End as an Access Database File Using Links to Views Stored on the Back-End

In this scenario, you can link the back-end views to the front-end application database (.MDB or .ACCDB). A view is like a query stored on the back-end. It always executes on the back-end, returning the requested rows and columns to the front-end Access application. Once returned to the Access application, the data is updateable as long as certain rules are met. For example, the view must contain a unique field. The process of linking to back-end views is almost identical to that of linking to back-end tables.

I particularly like using views in my client/server applications. This is because views give me the best of both the Access and client/server worlds. Using views I can easily ensure that processing is completed on the server, therefore taking full advantage of the client/server environment. But, because views results are almost always updateable, I can take advantage of the richness of bound forms in Access.

The Front-End Using SQL Pass-Through to Communicate to a Back-End

If a particular query is running inefficiently, you may want to bypass ODBC and go directly against SQL server. You do this using a SQL pass-through query. Here are a few reasons why a SQL pass-through query may be the best option available in specific situations:


You execute a pass-through query written in the syntax specific to the back-end database server. Although the query does pass through the Access Database Engine, the Access Database Engine does not perform any translation on the query. Neither does ODBC. The ODBC Manager sends the query to the ODBC driver, which passes the query to the Back-End without performing any translation. In other words, exactly what was sent from Access is what is received by the SQL database. Figure 3 illustrates this scenario. Notice that the Access Database Engine, the ODBC Manager, and the ODBC driver are not eliminated entirely. They are still there, but they have much less impact on the process than they do with linked tables.



Pass-through queries are not a panacea, although they are very useful. The results of a pass-through query are not updateable, for example. Furthermore, because pass-through queries are written in the Back-End's specific SQL dialect, you must rewrite them if you swap out your Back-End. For these reasons and others, you will generally use pass-through with other solutions.

The Front-End Executing Procedures Stored on a Back-End

A stored procedure is compiled SQL code stored on a back-end. You will generally execute it using ActiveX Data Objects (ADO) or Data Access Objects (DAO) code. You can also execute a stored procedure using a pass-through query. Regardless of how you call it, the code within the stored procedure is written in the SQL native to the back-end on which it is stored, and the stored procedure is executed in its entirety on the back-end. Stored procedures can return results or can simply execute on the back-end without returning any data. When I need to pass parameters (such as a start date and end date) to SQL Server, or I simply want to update data on the server without returning any data to the client, I use stored procedures.

The Front-End as a Microsoft Access Data Project Communicating Directly to a Back-End

ADP files were introduced in earlier versions of Access. Although for a while they were considered the technology to use, it turned out that ADP files were the database technology du jour. In fact, there is no upgrade path for an ADP file in Access 2007 and Access 2010, and therefore using ADP files, you cannot take advantage of the features added to Access 2007 and Access 2010.

Summary

Most of the applications that I build use a combination of linked tables, linked views, pass-thru queries, and stored procedures. Knowing when to use each one is an art rather than a science. It takes a lot of practice, experimentation, and real-life experience to get the perfect mix of these techniques in the applications that you build.