Introduction to Client/Server Design

In a file-server system, a copy of Microsoft Jet is running on each workstation. Microsoft Jet performs most processing on the workstation; only a minimal amount is performed on the server. The server responds to requests for data from files.

A Microsoft Jet file-server application must move a fair amount of data back and forth across the network to satisfy a user’s requests for data. For example, to edit a record on a file server, Microsoft Jet needs to read an index, retrieve the data, read and write to the lock file, and then update the index and the database itself. The performance and simplicity of the file-server architecture make it ideal for prototyping large multiuser systems and creating small- to moderate-sized applications.

See Also For more information on how the file-server environment works, see Chapter 6, “Creating Multiuser Applications.”

There are certain applications that exceed the capabilities of file-server systems. For example, once an application exceeds a certain number of concurrent users or becomes mission-critical, there are performance, security, and data integrity issues that arise that no file-server system can accommodate fully. The client/server architecture meets these requirements, but it requires you to think about your database development efforts in new ways.

A Microsoft Jet and DAO client/server application uses an ODBC driver to connect to a remote database stored on an ODBC server such as Microsoft SQL Server 6.5. In a client/server application, a front-end (or client) application, such as a Visual Basic or Microsoft Access application, runs on a local workstation and uses Microsoft Jet (or ODBCDirect) and DAO to send requests to a back-end database stored on an ODBC server. These requests take the form of SQL statements that are translated to the server’s dialect of SQL by the ODBC driver (when using Microsoft Jet or ODBCDirect), or passed directly to the server (when using pass-through queries). In a well-written client/server application, most of the processing occurs on the server itself before data is returned to the client application, which reduces network traffic. Additionally, the computer used to run an ODBC server is often more powerful than a typical file server, so it can handle more requests and process each request more quickly.

To get a general idea of some of the differences between file-server architecture and client/server architecture, consider the simple task of opening a table. The following DAO code shows how to open a table and a Recordset object in a file-server system. In this code, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database
Dim rst As Recordset
Dim strName As String
	
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Employees")
strName = rst!LastName
Debug.Print strName

The preceding code performs the following operations:

1. Microsoft Jet opens the file NorthwindTables.mdb, a very inexpensive operation in terms of time and resources.
   
   
2. The Microsoft Jet dynamic-link library (DLL) looks inside the database file for information about a table named Employees. This results in several relatively inexpensive disk reads and some memory manipulations on your user’s machine.
   
   
3. Microsoft Jet loads details about the Employees table into the memory on your user’s workstation, such as how many fields it has, what the data types are, and which indexes are available, as well as information about exactly where in the database file the first data page can be found. Because an .mdb file is the native file format for Microsoft Jet, this operation is performed relatively quickly.
   
   
4. Microsoft Jet reads the data from disk and makes it available to the user’s application.

In the file-server example, the OpenDatabase method is used to access data in a database. In a client/server system, there are several ways to access ODBC server data. You can:

• Open an .mdb file and retrieve data using a linked table in the database.
  
  
• Open an ODBC data source using the OpenDatabase method of a Microsoft Jet Workspace object.
  
  
• Open an ODBC data source using the OpenConnection method of an ODBCDirect Workspace object. This method works directly with the ODBC driver and doesn’t require loading the Microsoft Jet engine into memory.
  
  
• Send a pass-through query to the ODBC server using Microsoft Jet.

In a client/server system, the following steps occur when you open a linked table or a table in a remote data source:

1. When you access data in an ODBC data source using one of the methods previously described, a connection is made to the ODBC server. This is a relatively expensive operation, which may take anywhere from a half second to several seconds.
   
   
2. When the ODBC server receives a connection request, it sets aside memory and other resources and prepares for other requests. It then replies with a connection.
   
   
3. Microsoft Jet or DAO uses the connection to issue a request to the server for the table data.
   
   
4. The server returns the requested data and makes it available to the application’s user.

If you read these steps carefully, you can see the fundamental difference between file-server and client/server architectures. In a file-server system, the database engine is completely responsible for physically retrieving the data from the database. It knows explicitly where in the file to find the data, and returns it directly to the user.

In a client/server system, the client (the local front-end application) formats a request for a specific set of data and passes this request to the database server using an ODBC driver. The client serves no other function in retrieving the data. It’s up to the database server to process the request and pass the data back to the client.