Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

The Roles Access Plays in the Application Design Model

This section takes a look at the many different roles Access can take in an application design.

The Front End and Back End as Access MDB Files

Earlier in this book, you learned about using 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 20.7 shows the architecture in this scenario. The Access application resides on the workstation. Access uses the Microsoft Jet Engine to communicate with data stored in an Access MDB database file stored on the file server.

The Front End as an MDB File Using Links to Communicate to a Back End

In the second scenario, you can link the back-end tables to the front-end application database (.MDB). 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, Paradox, or dBASE. You can also treat the linked tables like any other linked tables. Access uses ODBC to communicate with the back-end tables (see Figure 20.8). Your application sends an Access SQL statement to the Access Jet Engine, which translates the statement into ODBC SQL. Jet 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. As you might imagine, all this translation takes quite a bit of time. Furthermore, ODBC is becoming a technology of the past; it is quickly being replaced by the ADO/OLEDB technology. That is why one of the two alternatives that follow might be a better solution.

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

One of the bottlenecks of using linked tables is waiting for all the translation to happen. Because of this and for the following reasons, you want to bypass the translation process:

• Access SQL might not support some operation that the native query language of the back end supports.

• Either the Jet Engine or the ODBC driver produces a SQL statement that is not optimized for the back end.

• You want a process performed in its entirety on the back end.

As an alternative, you can execute a pass-through query written in the syntax specific to the back-end database server. Although the query does pass through the Jet Engine, Jet 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 20.9 illustrates this scenario. Notice that the Jet 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 attached 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 ADO or Data Access Objects (DAO) code. You can also execute a stored procedure using a pass-through query. Regardless of what 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.

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

An additional, very viable solution is available when working with a back-end database server. This involves using a Microsoft Access Data Project (.adp), which was introduced with Access 2000. By using a .adp, you bypass the Jet Engine entirely. An Access project contains only code-based objects such as forms, reports, data access pages, macros, and modules. All tables, views, database diagrams, functions, and stored procedures are stored in a SQL Server database. After you have connected with a SQL Server database, you can easily view, create, modify, and delete SQL Server objects. Figure 20.10 illustrates this scenario. Notice that neither the Jet Engine nor ODBC is involved in the scenario.