BUG: Excessive Network Activity When Using Access ODBC Driver

When using an MDAC 2.1 version of the Microsoft Access ODBC driver and opening a Microsoft Access database file on a network share, an excessive amount of network packet activity occurs, even when the ODBC connection is idle. In addition, when using a local database file, continuous file activity occurs even when the ODBC connection is idle.

The PageTimeout setting for the ODBC data source (DSN) or supplied in the ODBC connection string is set too low. The Microsoft Access ODBC Driver that ships with MDAC 2.1 or later interprets the PageTimeout setting in milliseconds. The MDAC 2.0 versions of the Access ODBC Driver interpreted PageTimeout in seconds.

Also, if you create a new ODBC data source with the MDAC 2.1 or later version of the Access ODBC Driver, the driver incorrectly uses a default value of 5 for PageTimeout. The correct default value should be 5000 with the MDAC 2.1 version of the driver.

The recommended setting for PageTimeout with the MDAC 2.1 or later version of the Microsoft Access ODBC driver is 5000. To change this value, click Options in the ODBC data source and modify the PageTimeout value.

You can also add this to your ODBC connection string. For example, here is an ODBC connection string that uses a DSN as well as adds the PageTimeout setting: Here is an example ODBC connection string that uses a DSN-less connection as well as adds the PageTimeout setting:

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.

In one test scenario, a total of 24,048 network packets totaling 14,380,704 bytes were generated in one minute by an idle ODBC connection using an Access 97 format database on Windows NT 4.0 file server. The amount of packet activity generated is dependant upon the processor speed of the client computer, the network protocol used, the responsiveness of the file server, and also the network client used; for example using Novell or Microsoft network clients can affect the number of packets.

To determine which version of the driver you have, start the ODBC Administrator tool and click the Drivers tab. If the version of the Microsoft Access ODBC driver starts with 3, you have an MDAC 2.0 version of the driver. If the version starts with 4, it is an MDAC 2.1 or later version of the Microsoft Access ODBC driver.

To reproduce this issue, you need a network packet monitoring tool such as Windows NT Network Monitor as well as the ODBC Test tool that is installed by the Microsoft Data Access SDK 2.1. Note that any ODBC client tool that can open an ODBC connection and leave it open can be used in place of ODBC Test.

Drivers required for Network Monitor are included in all versions of Windows NT and Windows 2000. The Network Monitor application required for monitoring traffic is included only in the server versions. For more information on Network Monitor look in the Help included with the operating system and MSDN.

Steps to Reproduce Behavior

1. On the client computer, create a new ODBC Data Source (DSN) named TestPageTimeout that uses an Access database that resides on another computer on the network.
2. On the ODBC Microsoft Access Setup dialog of the new DSN,click Advanced to confirm that the PageTimeout setting has a value of 5.
3. Start ODBC Test then Click Connection and select Full Connect.
4. Select the TestPageTimeout data source created in step 1 and click OK.
5. To activate the PageTimeout related packet activity, open the remote database file using another client and make a change to the database. For example, opening the remote database in Microsoft Access and modifying any record in any table suffices.
6. Wait about 60 seconds and begin monitoring network packet activity between the client and server computers. Note that even though the ODBC connection opened by ODBC Test is idle, a large amount of continuous packet activity between the client and server is generated.

For more information on the PageTimeout setting, see the Microsoft Jet Database Engine Programmer's Guide, Second Edition (Microsoft Press).