KNOWING WHAT TO WATCH FOR IN APPLICATION DEVELOPMENT
When developing client/server applications, you need to watch out for many things if you plan to migrate to client/server. These include—but aren't limited to—limiting your data, using combo boxes, using Access-specific and user-defined functions, creating heterogeneous and cross-database joins, dealing with ActiveX objects, and using local Access tables.
Limiting Your Data
In client/server development, it's very important to limit the amount of data being sent back and forth across the network and to retrieve only the columns and rows you need. If you read nothing else in this section, leave it with the message that limiting your data is crucial.
Moreover, design your forms with this in mind. The goal is to limit the amount of data that initially appears on a given form. Ideally, you could create a form that doesn't require a call to the server at all. Take the approach of retrieving data on an as-needed basis. Why spend 30 seconds retrieving an entire data set when the user might need to work with only one of those records? It's more efficient to retrieve only what you need as you need it, in many cases.
For example, you could create a customer form that's bound to a table and let users navigate through the form's records to find the desired customer record. When this form opens, it pulls large amounts of data down from the server. Alternatively, you could create a form that lists all customer names, let users select a single customer from the list, and then open the customer form with a WHERE clause that pulls in the single desired customer record from the server.
Tip
This applies not only to rows of data, but also to columns. Avoid selecting all columns from any given table when at all possible. If you don't need all the fields, don't drag them across your network!
Also consider your decision of whether to use a dynaset or snapshot recordset object. Keep in mind how these recordset objects differ. A dynaset sends two queries to the back-end server—one to bring back enough data to fill the first visible page and a second to bring back the primary key values for the remaining records. A snapshot, on the other hand, brings back the entire data set in read-only mode. For larger data sets, both objects can put an unnecessary burden on your network. However, in general, for small- to medium-sized query result sets, a dynaset gives a better response time because it continues background fetching of the keys only as you peruse the first page of data.
Using Combo Boxes
Combo boxes are a great feature in Access that you should use all the time, right? Well, a word of caution is necessary. As your data set grows, the usefulness of the combo boxes can soon be outweighed by the performance degradation you'll experience. A combo box that presents 25 items makes sense, whereas a listing of 1,000 items is slow, and it's unreasonable to expect the end user to stumble through such an unmanageable list. Further, combo boxes on forms might keep ODBC connections open to the server, placing an additional burden on network and server resources. When migrating over to client/server, consider whether it makes sense to continue using combo boxes in every case.
You need to consider alternatives, such as using popup forms or providing a means of filtering your combo boxes to make selection easier. If the value list in the combo box is static, consider using VBA code to fetch data items for the combo box from the server at form load time and using the data to create a static string for the combo box's RowSource property.
Using Access-Specific and User-Defined Functions
Access provides great flexibility in the development of queries, such as the capability to include user-defined functions, immediate IF statements (IIF), and the TRANSFORM verb for crosstab queries. Unfortunately, SQL Server doesn't understand these Access-specific syntaxes. If queries of this sort exist in an application upsized to use SQL table links, Access processes the query locally after the server sends the entire data set back across the network. If queries of this sort exist in an application upsized to an Access Project, the queries generally aren't converted by the Upsizing Wizard. Be sure to remove any function types or Jet-specific syntax from your queries before upsizing. See the later section “Cleaning Up Queries” for more information.
Note
For an application that will be upsized to use SQL table links, you should also avoid DAO code that operates on table-type recordsets, such as the OpenTable method (pre–Access 2000), the OpenRecordset method of type dbOpenTable, and the Seek operation because you can't use these with tables linked from SQL Server. For an application that will be upsized into an Access Project, use only the newer ADO code in your modules, not DAO code (see the help topic “Converting DAO Code to ADO” for hints on this). The DAO code won't run after it's upsized into a Project.
Creating Heterogeneous and Cross-Database Joins
SQL Server 6.5 can handle only local joins; heterogeneous joins and cross-database joins aren't supported. A heterogeneous join is a join between two different database formats, such as Access and SQL Server; a cross-database join is a join between two tables in the same format but in different locations. While developing applications that will be upsized to SQL Server 6.5, try to keep data that will be joined in queries in the same format and in the same database to avoid the limitations of heterogeneous and cross-database joins.
SQL Server 7.0 is freed from such limitations through a concept called a linked server, which is a defined data source known to the SQL Server database that points to another database server. You can refer to linked servers in SQL statements and views in Access Projects, providing for cross-database joins that might or might not also be heterogeneous.
Dealing with OLE Objects
Just as they are in Jet tables, memo fields and OLE objects such as bitmap pictures or video clips can be a tremendous burden on query execution in SQL Server. Rather than display these field types automatically, exclude them from SQL statements and queries/views unless they're needed. This will minimize network traffic in a file-server or client/server application.
Tip
One way to minimize network traffic is to base your form on a query that selects only non-OLE and non-memo fields. Then create a second query to retrieve your OLE objects and memo fields on an as-needed basis that can be launched via a command button or menu selection.
Using Local Tables for Static Information
In cases where you upsize an application to use linked SQL Server tables, you might be able to increase your client/server application's performance dramatically by loading some data into local temporary tables during the application startup routine. Suppose that you have 100+ fairly static categories for the banking transactions in your application. If these codes populate a combo box on a number of your forms, it makes sense to store these values locally. Be sure to create an index on the local temporary table, as well.
If you decide to store locally data that can change during the course of the day, you should consider providing users with a way to refresh these local temporary tables.
To use local tables, first create the table structure in Access. Then write a function that queries your SQL Server database when your application starts up. This function should replace existing table values with the current values from SQL Server. (For more details about performing this task, see Chapter 28, “Creating Maintenance Routines,” in which an example already exists.)
This concept must be implemented differently in an application upsized to an Access Project, because a Project can't contain local (Jet) tables. One approach is to pull the required temporary values from the SQL server into a local VBA array and use the array to populate form controls as needed. Another approach is to use ADO code to store data locally in an .mdb file that supports the Project but isn't physically linked to it.