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:
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:
In a client/server system, the following steps occur when you open a linked table or a table in a remote data source:
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.