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