- Why This Chapter Is Important
- Understanding the Client/Server Model
- Deciding Whether to Use the Client/Server Model
- The Roles Access Plays in the Application Design Model
- Learning the Client/Server Buzzwords
- Upsizing: What to Worry About
- Proactively Preparing for Upsizing
- Using the Upsizing Wizard
- Defining an ODBC Data Source
- Connecting to a Database Server
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 3.3 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.
)
Figure
3.3 Access as a front end using an MDB file for data storage.
The Front End as an MDB File Using Links to Communicate to a Back End
In the second scenario, back-end tables can be linked 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. And you can treat the linked tables just like any other linked tables. Access uses ODBC to communicate with the back-end tables. (See Figure 3.4.) Your application sends an Access Structured Query Language (SQL) statement to the Access Jet Engine, which translates the statement into ODBC SQL. Access 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 the 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; the ADO/OLE DB technology is quickly replacing the ODBC older technology. That is why one of the two alternatives that follow might be a better solution.
)
Figure
3.4 Access as a front end using links to back-end tables.
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 Access sends is what the SQL database receives. Figure 3.5 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 covered in more detail in the "Using Pass-Through Queries" section later in this chapter.
As you will see later in this chapter, 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 you write pass-through queries 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 generally use pass-through queries along with other solutions.
)
Figure
3.5 Access sending a pass-through query to a back-end database.
The Front End Executing Procedures Stored on a Back End
A stored procedure is compiled SQL code stored on a back end. You generally execute stored procedures using ADO or DAO code. You can also execute a stored procedure using a pass-through query. Regardless of what you call it, you write the code in a stored procedure in the SQL native to the back end on which it is stored, and SQL Server executes the stored procedure 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 Microsoft Access Data Project (.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 3.6 illustrates this scenario. Notice that neither the Jet Engine nor ODBC is involved in the scenario.
)
Figure
3.6 Access using a Microsoft Access Data Project to communicate to a back
end.