- 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
Connecting to a Database Server
After you define a data source, you are ready to connect to it. You can use these methods to access server data:
Link to tables residing on the server
Link to views residing on the server
Use pass-through queries to send SQL statements directly to the server
Use VBA code to open the server tables directly
Create an Access Data Project (with this option, you do not define an ODBC data source)
Working with Linked Tables
The easiest method of accessing data on the server is to link to the external tables. These linked tables act almost exactly like native Access tables. When you link to remote tables, Access analyzes the fields and indexes contained in the tables so that it can achieve optimal performance. It is important to relink the tables if the structures of the remote tables change. You can use the following techniques to link to external tables:
Linking to external tables via the user interface
Linking to external tables using code
Linking to Views Rather Than Tables
Views on a database server are similar to Access queries. Views provide a form of security by limiting which rows and columns a user can see. You give access to the view rather than directly to the underlying table. By default, views are not updateable. You can make a view updatable by including all the fields that compose the primary key in the view and building a unique index on the primary key. You can create views in one of three ways:
Using the SQL Server Enterprise Manager for SQL 7.0 or SQL 2000 (or the equivalent option for your back-end database server)
Using the Create View statement in Access
Using the View tab of an ADP file
NOTE
Chapter 11, "Developing an MDB Client/Server Application with Linked Tables," covers the process of working with linked tables in detail.
Using Pass-Through Queries
Ordinarily, store and execute a query in Access, even if it is running on remote data, Access compiles and optimizes the query. In many cases, this is exactly what you want. On other occasions, however, it might be preferable for you to execute a pass-through query because these queries are not analyzed by Access's Jet Engine. These queries are passed directly to the server, and this reduces the time Jet needs to analyze the query and enables you to pass server-specific syntax to the back end. Furthermore, pass-through queries can log informational messages returned by the server. Finally, bulk update, delete, and append queries are faster using pass-through queries than they are using Access action queries based on remote tables.
Pass-through queries do have their downside. They always return a snapshot, rendering them not updatable. You also must know the exact syntax the server requires, and you must type the statement into the Query window instead of painting it graphically. Finally, you cannot parameterize a query so that it prompts the user for a value.
Creating a Pass-Through Query in an MDB File Using the User Interface
To create a pass-through query, you can build the query in the Access query builder. Choose Query, SQL Specific, Pass-Through. You are presented with a text-editing window where you can enter the query statement. The SQL statement that you enter must be in the SQL flavor specific to your back end.
Executing a Pass-Through Query Using Code
You also can perform a pass-through query by using VBA code. In fact, you must create the pass-through query by using VBA code if you want the query to contain parameters that you will pass to the server.
NOTE
Chapter 11 covers the process of working with pass-through queries in detail.
Executing and Creating Stored Procedures
You can execute stored procedures on a back-end database server. A stored procedure is similar to a query or program stored on the back end, and it performs some action.
Executing a Stored Procedure
An example is the SQL Server 2000 stored procedure named sp_columns. This stored procedure returns information on the fields in a particular table. Figure 3.22 shows how you would execute the sp_columns stored procedure from the Query Design window. You simply type the name of the stored procedure and any parameters it must receive. Take a good look at the Query Properties window shown in Figure 3.22. If you enter a valid ODBC connect string, the user is not prompted to log on at runtime. The Return Records property is another important property. In this case, you want to set the value of the property to Yes so that you can see the results of the stored procedure. If the stored procedure does not return records, it is important to set this property to No. Otherwise, you receive an error message indicating that no rows were returned. Figure 3.23 shows the results of running the stored procedure.
){kind=small}
Figure
3.22 If you enter a valid ODBC connect string in the Properties window,
Access does not prompt the user to log in at runtime.
){kind=small}
Figure
3.23 The result of running the sp_columns stored procedure.
Creating a Stored Procedure
If your application is an ADP file, you can create stored procedures directly from the Access environment. If your application is an MDB file, you can create a stored procedure using a pass-through query. In either case, you can create a stored procedure using the SQL Server Query Analyzer or the SQL Server Enterprise Manager.
NOTE
Chapter 8, "Designing SQL Server Stored Procedures, User-Defined Functions, and Triggers," and Chapter 11 cover the process of creating and working with stored procedures in detail.
Using a Microsoft Access Data Project to Access Client/Server Data
As mentioned earlier in this chapter, Access projects (ADP files) enable you to work with SQL Server databases without loading the Microsoft Jet Engine. Access projects have the following advantages over the other methods of accessing client/server data covered in this chapter:
They provide you direct access to a Microsoft SQL Server database.
You can easily create and modify SQL Server tables, views, database diagrams, and stored procedures all from the Access development environment.
You can build forms, reports, data access pages, and modules that access SQL Server data, without involving Jet.
Fewer resources are required on the client side.
The server is responsible for all query processing.
You can access functionality not available when accessing ODBC via Jet.
You can execute asynchronous queries. This means that you don't have to wait for a query to complete execution before you begin another operation.
You can perform batch updates. This means that you can cache changes locally and then submit them to the server as a batch.
You can run queries that return multiple resultsets.
You easily can limit the number of records returned in a resultset.
You easily can monitor messages and errors generated by the remote data source.
The disadvantages of Access Data Project files follow:
You cannot create local tables.
You cannot create local queries.
There is a learning curve associated with the process of becoming proficient in working with ADP files.
The process of creating tables, views, database diagrams, and stored procedures involves a learning curve.
NOTE
Chapter 12, "Developing an ADP Application," covers the process of working with ADP files in detail.