Visual Basic Concepts

Managing DAO Network Traffic

When using DAO with the Jet engine, a primary consideration is the amount of data that your network is required to carry. This is especially true if your design includes a shared Jet database that contains local, unattached data. In this case, the network will carry all disk I/O traffic as multiple users compete for shared data pages. If the shared database is simply a repository for one or more attached tables, in most cases only the query results need to be transmitted over the network.

Your application can control network traffic indirectly, through judicious use of Recordset object size and choice of query processor. In many cases, the Jet query processor can create Recordset objects with comparatively little network traffic. However, some designs may not accommodate its use, and may consequently create more network traffic than would occur with other programming models. By tuning the SQL query passed to the Jet query processor, you can often make better use of its power while improving network performance.

When accessing attached tables with the Jet engine, only the linkage information and the results of the query need to be transmitted over the network. If, however, the query processor is forced to download part or all of a remote table, network load increases dramatically.

Using a wide area network (WAN) with a Jet database is possible, and with careful error management, WAN applications can be implemented with a degree of security. Your design should, however, take additional precautions and include extremely robust error management that anticipates the loss of network access to the remote server and often dramatically longer response times. Since WAN networks can be significantly slower than conventional local area networks, special care should be given to the amount of network traffic generated and DAO timeout values. Examine the SQL trace logs for a better understanding of the number and complexity of the queries generated to remote ODBC servers. It is always good design practice to use more robust error handling for all network operations regardless of the topology.

Enabling Trace Logs

One of the most helpful debugging and tuning tools you have at your disposal is the ability of the ODBC Driver Manager to log all ODBC operations to an external file. You can enable this file using the ODBCDirect LogMessages property or by selecting the associated option in the Windows control panel ODBC Administration dialog. Be sure to turn off logging before your application goes into production, as the logging process can significantly impact performance.

Another option available to Microsoft SQL Server developers is the new SQLTrace utility that can let developers interactively view the queries submitted by all applications against a SQL Server. Once started, the SQLTrace utility exposes a window that displays each query or other operational request made.

For More Information   See “Choosing a DAO Query Processor for Use with Jet” and “Managing DAO ODBC Connections with Jet.”