You are using Access as a front end to linked tables stored in a client/server database. You're not satisfied with the response time of your client/server application. What can you do to make it run faster?
You can apply a variety of optimization techniques when developing client/server applications. If you are attaching remote tables in databases such as SQL Server or Oracle, you are accessing data through open database connectivity (ODBC) drivers. Typically, client/server applications using ODBC require more horsepower on the part of workstations and the network. By knowing how data is retrieved from the server, you can make your application run faster.
Another option is to create an Access Data Project (ADP). This is possible only if your data is stored in SQL Server. Instead of using ODBC, ADPs use a newer technology, OLE DB, to connect to the data. However, although OLE DB is newer, it isn't necessarily faster than linking to tables using ODBC. Chapter 14 includes several solutions related to the use of Access project applications.
There is no sample database for this solution. Here are some suggestions to consider when optimizing your linked-table client/server application:
Your forms should retrieve as few records as possible when loading (fetching data is a significant bottleneck in client/server applications). Design your form to retrieve few or no records by using the technique demonstrated in the Solution in Recipe 8.7.
Optimize the way your application connects to the server. When the user starts your application, log the user into the server using the OpenDatabase method. This establishes a connection and caches it in memory. Subsequent data access is faster because the connection has already been established. Use code similar to the following:
Sub PreConnectUser (strUser As String, strPass As String) Dim wrk As DAO.Workspace Dim db As DAO.Database Dim strConnect As Database strConnect = "ODBC;DSN=MyServer;DATABASE=dbCustomers;" _ & "UID=" & strUser & ";" _ "PWD=" & strPass & ";" Set wrk = DBEngine.Workspaces(0) Set db = wrk.OpenDatabase("", False, False, strConnect) End Sub
Reduce connections by limiting recordsets to 100 records or fewer. Most servers (such as SQL Server) require two connections for recordsets of more than 100 records. By limiting the size of the recordset, you reduce the number of connections that need to be made, speeding up your application.
Offload as much query processing as possible to the server. Generally, your server will search and process data faster than the local Jet engine, especially if there are many concurrent users (this is probably the reason you moved to client/server in the first place). Design your queries to eliminate expressions or functionality not supported by the server. If the server does not support an expression or function used in your query, Access will process the query locally and performance will suffer. Read the documentation that comes with your database server to determine which functionality is supported, and use profiling tools on the server (like the SQL Server Profiler) to see what is actually being processed on the server.
Add a timestamp field to a table to improve update and deletion performance. The server automatically updates Timestamp fields, also called Rowversion fields, when any data in a row is modified. If a table has a Timestamp field, Access can use it to determine quickly whether a record has changed. If the table doesn't have this field, Access needs to compare the contents of every field to see if the record has changed. Obviously, checking a single field is a lot faster. To add a Timestamp field to a table on the server, you can create and execute a SQL-specific query in Access using the ALTER TABLE statement with syntax similar to the following:
ALTER TABLE Customers ADD MyTimeStampCol TIMESTAMP
Avoid using server data to fill list box and combo box controls. The performance of these controls is generally poor when accessing server data. Instead, consider storing the data for the list box or combo box in a local database. This approach works if the data does not change frequently and can be easily copied from the server. See the Solution in Recipe 8.2 for more on list box and combo box performance issues and alternatives to their use.
For working with server data in code, ADO is more efficient than DAO. We can't discuss ADO coding techniques fully here, but take the time to learn ADO if you want to fill recordsets with server data or to execute server commands. (On the other hand, DAO recordsets tend to be more efficient, and simpler to use, when working with Jet-based data?data retrieved from MDB or MDE files.) Pay special attention to the CursorLocation property, which allows you to close a connection and still be able to work with the data in a client-side ADO recordset. Here is an example of opening a client-side recordset, disconnecting from the database, and then working with the data in the cached recordset:
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim strEmployees As String Set cnn = New ADODB.Connection cnn.Open "Provider=SQLOLEDB.1;" _ & "Data Source=(local);Initial Catalog=Northwind;" _ & "User ID=username;Password=secretpwd" Set rst = New ADODB.Recordset rst.CursorLocation = adUseClient rst.Open _ Source:="SELECT EmployeeID," _ & " LastName, FirstName" _ & " FROM Employees" _ & " WHERE EmployeeID = 5", _ ActiveConnection:=cnn, _ CursorType:=adOpenStatic, _ Options:=adCmdText Set rst.ActiveConnection = Nothing cnn.Close Set cnn = Nothing Debug.Print rst("FirstName") rst.Close Set rst = Nothing
Understanding how client/server applications differ from single-user and file-server applications is crucial to optimizing their performance. The key is in deciding when to let Access do the work and when to let the server do the work. With a few exceptions, you want the server to perform queries and Access to perform user-interface operations. Concentrate on minimizing the traffic across the network by reducing the data retrieved from and written to the server. To work with server data programmatically, use ADO rather than DAO.